I need to setup repliaction between A and B. I have high transaction /min count. Some tables have TEXT columns. This is what i am planning to do:
Example: table name is CREDITS
vertical partition the table into two. First table (will be called CREDITS_PRI) holds PK column and non TEXT columns, second table (will be called CREDITS_SEC) holds PK column and TEXT columns.
Create a view called CREDITS with INSTEAD OF triggers for inserts, updates and deletes. So far so good. Then setup replication with immediate updating subscription between A and B for tables called ..._PRI and merge replication between A and B for tables called ..._SEC.
Would this work? How do other companies handle this? thank you in advance for pointing me into the right direction.
I have a SQL date problem that I just cant get to the bottom of. A SSIS package runs that inserts dates into a SQL table from strings in the format dd/mm/yyyy This package is run from within a SQL job. Unfortunately the dates are being interpreted as mm/dd/yyyy. I have checked every possible date setting that I can, but nothing seems to work (SQL Agent account lang is set to British English, collations, Locale ID setting in the SSIS package, Lang of account that runs the SSIS service).
When the package is run in BIDS it inserts the dates correctly, just not from a SQL job. I have tried inserting SET DATEFORMAT dmy in an Execute SQL Task in the SSIS package but that does not work either.
The baffling thing is that it works on one server but not another. Both servers have the same collation, regional settings and use the same SQL Agent account.
Also when SELECT @@Language is used is this determined by the lang setting against the user ?
Folks i have been dumped into the frying pan. Put in hours and hours of work and research over it already but still can't seem to figure out what the heck is going on. Can an expert listen to my troubles and perhaps share some hints. Thank you so much for reading.
Enviroments Windows 2003 SP2 x64 SQL 2005 SP2, Reporting service IIS6 no SSL/Cert with .NET SP1
Both ReportServer and ReportManager was working prior to an unfortunenate event, installing Exchange 2008.
Immediately after the installation of Exchange 2008 we experienced 2 problems 1. SSL certs was now required, which we resolved by unchecking the SSL required option in IIS6 2. After SSL, execution not found occured in ReportManager
Immediately after we discovered the Execution error, we went over logs after logs to see whats going on. Nothing worked We have, uninstalled Exchange, Uninstalled Reporting service, Reinstalled reporting service, updated report viewer to sp1, increased the timeout limit to 5 minutes, checked and rechecked our login/password on sql asp w3k reporting and till now still no go.
<-- Error messeage --> This error msg in reporting service i believe is the reason why we are getting Execution not found. w3wp!session!1!01/23/2008-11:14:02:: i INFO: LoadSnapshot: Item with session: g4bqhtq0pvzpxh55leja4445, reportPath: /Report Bank/Deposit by Account, userName: NT AUTHORITYNETWORK SERVICE not found in the database
and
w3wp!library!1!01/23/2008-11:14:02:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ExecutionNotFoundException: Execution 'g4bqhtq0pvzpxh55leja4445' cannot be found, ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ExecutionNotFoundException: Execution 'g4bqhtq0pvzpxh55leja4445' cannot be found <-- Error messeage -->
After reading tons of info, In our case our reports are simple reports, i can generate the report without problem from ReportServer in less than 10 seconds, just without the bells and whisles. But the second i hop on Report manager it will fight me till death with Execution. I think in our case, Its throwning a Username not found in database which might be preventing the creation of session key or the error is causing the render not to happen. If render dosen't happen then no key can be assigned to it?
We have tried to use impersonate via the web.config and also changing Windows account logins etc but none of them work. One thing that is consistent is that the userName: NT AUTHORITYNETWORK SERVICE not found has NEVER changed even if we impersonate or manually hardcode the username.
I seem to have run across the feared "Error: Subreport could not be shown" problem. I have very carefully checked everything I could find on the issue, but nothing seems to help--and I must admit, I'm at my wits' end here (though it's probably something absurdly simple).
I have a main report with one subreport. I am running ASP.NET, with a ReportViewer control on my ASPX page. The main report and subreport are both local reports, and pull data from SQL data sources.
Initially, I had the parent report pass a parameter to the subreport, but in the interest of keeping things simple I removed that--even though I verified that my handler for the subreport processing was valid and was obtaining valid values for the parameter.
Here's the applicable code from the CodeBehind's class:
Through debugging and other means, I have found the following:
1. The data being passed through vw in the ProcessSubReports is valid. 2. The subreport runs perfectly when placed into its own ReportViewer 3. The reports are all in the App_GlobalResources folder 4. The main report references a subreport by the name of SalesByProduct 5. App_GlobalResources/SalesByProduct.rdlc is the name of the report file
I should be able to provide more information on request.
I have built a web application that runs on my local web server against my local SQL server and my development server, but now I'm trying to get it to work on the live server and I'm not getting a connection. Whenever I try to open the connection, I get a. SQL Server does not exist or access denied. error. Here is my connection string... Public connString as string Dim SQLServer As String = "xxx.xx.xx.xxx" connString = "server=" & SQLServer & ";" _ & "database=DBName;Trusted_Connection=No;user id=xxxID;password=xxxPassword;" myConnection2 = New SqlClient.SqlConnection(connString) ------------------------------------------------------------ I'm starting my connection to run a datareader this way... Dim strSQL As String
strSQL = "SELECT ClientID, ClientName " & _ "FROM Client; " ' Create a connection to the table in the SQL database located on ' the remote computer. Dim myCommand2 As New SqlClient.SqlCommand(strSQL, myConnection2) myCommand2.CommandTimeout = 1200 myConnection2.Open() 'HERE IS WHERE THE ERROR OCCURS Dim myReader As SqlClient.SqlDataReader = myCommand2.ExecuteReader(CommandBehavior.CloseConnection) While myReader.Read() --------------------------------------------------------------- I can communicate with the server with no problem from my local computer. I can ping the server, and I also tested it by creating a system DSN using the login and password in my application, and it finds the database with no problem. I've tried to connect using the SQL Server's server name and the IP address and both return the same result. I've also saw something in another post to set the trusted connection parameter to "yes" and that didn't work either. I'm developing on a Windows XP Professional and I'm trying to connect to a Windows 2003 Server with SQL Server 2000 installed on the machine. I did add the database by restoring a backup from my local server onto the live server. I don't think that should matter, but maybe it does. If anyone has any ideas, I would greatly appreciate it.
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
I have the error above, but no error log. I can preview the sub report - but this main report fails after working this morning. This is for internal company reports and I rebuilt this one after converting from access. I have looked where the error logs should be, but there are no error logs. I rebuilt the query as I needed to change this, but this did not help. Is there someone who could point me in the correct direction.
Hi, I am mapping the .csv files to a destination table in sql server. The names of each column in the .csv file is the same as the ones in the destination table. In the destination table there is an extra field i.e. FileTypeField
Here is the question; When transfering the data from .csv to the destination table i.e flat file source to oledb destination, I would like to place a text into the field FileTypeField I do not see an option to do this. Can you help please? Thanks
HiI have to create an XML file based on a SQL SERVER 2005 table.Everything works fine:SELECT 1 as Tag, NULL as Parent, Ex_Id as [Exam!1!Ex_Id], NULL as [Ex_Title!2!!cdata] FROM Exams WHERE ex_State = 'P' UNION ALL SELECT 2 as Tag, 1 as Parent, Ex_Id, ex_Title FROM Exams WHERE ex_State = 'P' order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata] FOR XML EXPLICIT, ROOT('Exams') BUT when i add another column, a text column, I get into trouble: SELECT 1 as Tag, NULL as Parent, Ex_Id as [Exam!1!Ex_Id], NULL as [Ex_Title!2!!cdata], NULL as [Ex_Situation!3!!cdata] FROM Exams WHERE ex_State = 'P' UNION ALL SELECT 2 as Tag, 1 as Parent, Ex_Id, ex_Title, ex_Situation FROM Exams WHERE ex_State = 'P' order by [Exam!1!Ex_Id], [Ex_Title!2!!cdata], [Ex_Situation!3!!cdata] FOR XML EXPLICIT, ROOT('Exams') The problem is clearly the fact that i have to sort on the text column, plus the fact that this column requires the CDATA tag enclosurePlease help me....i'm desperate :(
Hi I had a text type not null column which i wanted to change to a null column.Writing a simple alter statement gave me an eror cannot change text type column so i tried to rename the original column create a new column with the same name and allowing nulls on it and then copying the contents of the renamed column to the new column and finally deleting the renamed column. EXEC sp_rename 'TableName.ColumnName', 'ColumnName_old', 'COLUMN'ALTER TABLE TableName ADD ColumnName text NULLUPDATE TableName SET ColumnName = ColumnName_oldALTER TABLE TableName DROP COLUMN ColumnName_old However when i tried to execute these statements in query analyser on the Update statement it gave me the error that ColumnName_old does not exist. However then I tried to execute these queries one by one I was able to do that. Can anybody tell me whats causing the queries to not be executed all at once without giving the ColumnName_old does not exist error cause I wanted to run them on live dbs.
how can i update a column with datatype of text with a combination of columns having a datatype of float? do i convert the float columns to varchar/char/?? and/or can i convert the column i am updating?
What would the syntax be to insert a column to the right of this one, and extract the first 8 digits from the data in the DATEID column and insert that into the new column DATE, therefore making it easier for me to query against an actual date?
Hi all i want to remove text from my column name using query. for example i have the product name like "silver 8' trampoline pack " i need to remove "silver 8' " and want to display only trampoline pack similarly if I have product name like "gold 8' trampoline pack" i need to display only trampoline pack. can anybody help me in this regard?
Hello,If I create a table with two columns of type "Text" will that cause anyissues? If my memory serves me correctly I thought I read a while backthat is it standard practice to only have one "Text" column per table.Any input would be appreciated.Thanks,Rob Panosh
I have a table with a TEXT column and this column contains an XML document. I'm developing a TSQL stored procedure which reads the content of this column and accesses the values in the XML elements but I have a lot of problems...
1) How can I read the text column and store the value in a variable? I tried
declare @a varchar(2048) set @a = (SELECT TEXT_COLUMN FROM MY_TABLE)
but it returns
Server: Msg 279, Level 16, State 3, Line 2 The text, ntext, and image data types are invalid in this subquery or aggregate expression.
I tried also with the READTEXT function but I just can't find how to store data read in a variable...
2) which length should I use for the VARCHAR variable which will store the data? Is it possible not to specify a length with SQL Server 7 or 2000?
I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.
Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?
Hi,How do we use like when we have a column of type TextSelect * from where myColumn LIKE 'prefix%'where myColumn is of type Text in SQL server 2005Thanks.
hi i have a table with a column which contains a large paragraph of data. i need to search for a pattern within this text and update it.
for example the column contains 4096 char. within this text, i search for a pattern"qwerty" and convert it to "asdfgh". any ideas how this can be done? i'm using sql server 2000 i have to write something like UPDATE [DB].[dbo].[Table] SET [Column1] = '.............', WHERE .......... any help will be appreciated.
I m using SQL Server 2000. I have Tabel named Topic and have a column name lineage. lineage has data like following: //////546707//546707//546707/43213/ Now I want to get records who has only one "/" in it's crreponding lineage column. Can somone tell me how to do that in SQL Server 2000? Thanks Khushbu
Hi Everyone, I have a simple webform with a text box with multiline enabled and a submit button, in vb.net. Inside the text box, when you are typing text with let's say adding carriage returns, spaces etc. How to i save the text to a database? By that, i mean, how to i declare the column in my database, varchar, text ...? Also, when retrieving the saved text back to the textbox, will it keep the formatting because that's what i am trying to do? Or, what is the best way to keep text formatting in a database? Thanks for the help, Kevin
Hi DBAs, I am very new in SQL server. I created a table where one column is varchar(8000). But when I am trying to insert value from enterprise manager this column cann't accept a long text value. I counted that its' capacity is 1012 charecters. I have tried a lot but don't know how to solve this. I really need help from you. Pls help.
A developer created a stored procedure that search in a huge table in a column with ‘like’ statement. I know that the best solution in most case is use of full-text.
But the content of this specific column is a XML data and Full-text don´t find words as desired.
For example: Table content: ID = 1 DsColumn = ´<Name>BETH</Name>´
select * from tbResp where DsColumn like '%BETH %' Results: IDDsColumn --- --------------------------------- 1<Name>BETH</Name> 1 row(s) affected
select * from tbResp where contains(DsColumn, ' BETH ')
Hello. I using a simply SELECT statement to retrieve some data from aSQL SERVER via an ODBC connection. I had to go from VARCHAR to TEXTbecause the amount of data. Anyway, my SQL statements worked just finewhen I was using VARCHAR, but now since I am using TEXT, I am onlyreceiving part of the content back. Do I have to do some sort ofspecial Casting or something if I want to get all the content back?It's over 8,000 characters. Thank you very much. I have racking mybrain on this for a while.
Hi Everyone, I´m new to this group, I´m trying to write a text filesadding content from a text column (more than 8000 characters), I foundcode how to write files and it works but i have the problem when addedthe text column to the body of the file.any idea? tip? thanks in advance! Pablo.
Hi All,We're running SQL Server 2000, SP3.I have a stored procedure that consists of a single Select statement.It selects a bunch of columns one of which is a column of data typeTEXT.SP takes 30 sec to run which causes timeouts on the Front End.When I comment out the Text column from the select it only takes 1Sec.Is there anything I can do about it? I know I can't index a Textcolumn. It's also not used in the where clause, so no need forFull-Text Search.But we absolutely have to have it in the Select clause.Thanks for the help in advance.~Narine
