Hello All,
Does anyone know if it is possible to send a text message to a phone number via a DTS package or job within SQL Server. For instance, if a job fails a text message is sent to a user.
Thanks, Gary
Hi
I have Outlook 2000 and SQL 7 .. do I need Window NT Messaging and Exchange Server to configure a profile for the mail to work ???
thanks in advance for the answer
I'm in the process of building messaging functionality in to my application where by users can contact one another, look at it as a dating site, you click on someones profile, view their profile and then send that user a message.
I started to build the table which looked like this:
Id (PK) (Increments by 1)
ToUserId (FK) -- User who they're getting in contact with
FromUserId (FK) -- User who sent the message
Content (nvarchar(3000)) -- Message being send
Status (int) -- read / new / deleted / sent
EmailDate (datetime)
EmailDeleted (datetime)
But the problem with this setup is both user's maybe sending / replying to each other so I would have multiple entries / statuses in one table which may become a nightmare to manage / control.
Need Some Help. I'm new to SQL Server 7.0., and I'm wondering if the application can do what I need it to do. I've got a stand alone third party program that updates a SQL 7.0 DB, and what I need is: When an update happens, is there any way to 'let another program know' that an update has occured and what the data is? I would hate to have to write an SQL clause to constantly 'poll' the DB looking for an update, this seems silly. Simply I need some way to know if table X has been updated, and what was the update? Any help would be greatly apprec... KT
View 1 Replies View RelatedTrigger seems to work fine, however, the Online Books state you can find out what data was actually modified when the trigger fired, but I can't seem to find out how to do it. Any Ideas Apprec.. Thanks.. KT
View 1 Replies View RelatedHi All, i am trying to host the system.messaging.dll in SQL Server. I get the usual warnings about not being fully trusted. I am prepared for MS not supporting. But, when i execute my trigger (which writes to message queue), the trigger fails with an exception of 'That assembly does not allow partially trusted callers'. So. is there no way for me to write to MSMQ using triggers? Do i have to rethink my strategy?
imports System.Messaging
Dim msmq As MessageQueue = MessageQueue.Create(".TestQueue", False)
msmq.Send("Test")
Thanks for the help
I need to develop an internal messaging sub-system that is similar toa web mail application but without SMTP support (e.g message routesare confined to the webapp domain). The requirements are rathersimple: Each user (e.g mailbox) can view incoming messages and hisoutgoing messages. Message quota is defined as the sum of all incomingand outgoing messages per userand tracked in the users' row (Users table – log_TotalMessages). Thequota is enforced by the business logic layer and not by the DB.I am considering the following data model for the storage component,and would appreciate community feedback:Table layout for incoming and outgoing messages************************************************CREATE TABLE [dbo].[Messages] ([MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID[RecipientID] [int] NOT NULL , // The userid ('Users'Table)[SenderID] [int] NOT NULL , // The userid ('Users'Table)[GroupID] [uniqueidentifier] NULL , // Only assigned if theuser "replyed" to an incoming message[SubmitDate] [smalldatetime] NOT NULL , // the date of themessage[DeleteBySender] [bit] NOT NULL , // Since I want to maintain onlyone copy of each message I mark a message "to be deleted" and deleteonly if both are true.[DeleteByRecipient] [bit] NOT NULL ,[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unreadmessages[Subject] [tinyint] NOT NULL , // Subject is derived from a fixedlist[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL) ON [PRIMARY]CREATE INDEX [Messages_RecipientID_IDX] ON[dbo].[Messages]([RecipientID]) ON [PRIMARY]CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])ON [PRIMARY]/* Send Message */CREATE PROCEDURE SendMessage (@IN_RecipientID int,@IN_SenderID int,@IN_GroupID uniqueidentifier,@IN_Subject tinyint,@IN_MessageText varchar(2000),@OUT_ERRCODE tinyint OUTPUT)ASBEGIN TRANSACTION SendMessageTransINSERT INTO Messages(RecipientID,SenderID,GroupID,SubmitDate,Subject,MessageText)VALUES (@IN_RecipientID,@IN_SenderID,@IN_GroupID,GETDate(),@IN_Subject,@IN_MessageText)UPDATE UsersSET log_NumberOfNewMessages = log_NumberOfNewMessages + 1WHERE usr_AccountNo = @IN_RecipientIDUPDATE UsersSET log_TotalMessages = log_TotalMessages + 1WHERE usr_AccountNo = @IN_SenderIDSAVE TRANSACTION SendMessageTransSET @OUT_ERRCODE = @@errorIF (@@error <> 0)BEGINROLLBACK TRANSACTION SendMessageTransENDELSEBEGINCOMMIT TRANSACTION SendMessageTransEND/* ReadMessage */CREATE PROCEDURE ReadMessage (@IN_MessageID int,@IN_RecipientID int,@OUT_ERRCODE tinyint OUTPUT)ASBEGIN TRANSACTION ReadMessageTransSELECT MessageText FROM Messages WHERE MessageID = @IN_MessageIDUPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =@IN_MessageIDUPDATE Users SET log_NumberOfNewMessages =log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @IN_RecipientIDSAVE TRANSACTION ReadMessageTransSET @OUT_ERRCODE = @@errorIF (@@error <> 0)BEGINROLLBACK TRANSACTION ReadMessageTransENDELSEBEGINCOMMIT TRANSACTION ReadMessageTransEND/* Delete Message */CREATE PROCEDURE DeleteMessage (@IN_MessageID int,@IN_DeleteIncomingMessage bit,@IN_DeleteOutgoingMessage bit,@OUT_ERRCODE tinyint OUTPUT)ASBEGIN TRANSACTION DeleteMessageTransDECLARE @Recipient intDECLARE @Sender intSET @Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =@IN_MessageID)SET @Sender = (SELECT SenderID FROM Messages WHERE MessageID =@IN_MessageID)IF (@IN_DeleteIncomingMessage = 1)BEGINIF((SELECT DeleteBySender FROM Messages WHERE MessageID =@IN_MessageID) = 1)BEGINDELETE FROM Messages WHERE MessageID = @IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @RecipientENDELSEBEGINUPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =@IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @RecipientENDENDIF (@IN_DeleteOutgoingMessage = 1)BEGINIF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =@IN_MessageID) = 1)BEGINDELETE FROM Messages WHERE MessageID = @IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @SenderENDELSEBEGINUPDATE Messages SET DeleteBySender = 1 WHERE MessageID =@IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @SenderENDENDSAVE TRANSACTION DeleteMessageTransSET @OUT_ERRCODE = @@errorIF (@@error <> 0)BEGINROLLBACK TRANSACTION DeleteMessageTransENDELSEBEGINCOMMIT TRANSACTION DeleteMessageTransEND/* ListIncomingMessages */CREATE PROCEDURE ListIncomingMessages (@IN_RecipientID int)ASSELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID= @IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC/* ListOutgoingMessages */CREATE PROCEDURE ListOutgoingMessages (@IN_SenderID int)ASSELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID= @IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESCThanks in advance!-Itai
View 4 Replies View Relatedhey all you database guru's hopefully someone can lend some insight as to a little table design problem I have.
Basically I've got a system in place to authorize users to access a website typical username password stuff. The table contains a list of users and there passwords plus the auth level and a few other tid bits that aren't really important enough to into detail about them here. What I want to do is add a messaging system to this, I think I could probably figure out a way to do this half decent if I setup a seperate table for each user to add a row to the table for every message entry than in my asp.net code have it delete everything but the last 10 entries every time a user logs on. However I would much prefer a way that I didn't have to setup a whole new table for each user just for messaging purposes, maybe store something like a list in one of the database cell's kind of like .nets generic.list or better yet generic.queue, I would also like a way if it's possible without too much work to have the table automatically delete the oldest message every time a new message is received if there's already 10 messages existing for the user.
Anyways hopefully someone has some experience in setting up a system like this, I don't really require any code samples I can code it all myself (other than the database code to automatically remove entry's, I'm not a database guy) if someone could just explain a way to accomplish what I'm trying to do, or if someone has a different more convenient way of doing this I would be up for suggestions
Thanks in advance for any help offered, I do appreciate it
Hello everyone !
I want to perform Full Text Search with SQL Server 2000. My documents (.doc, .xls, .txt, .pdf) are stored in a SQL Server field which is binary (the type of the column is image).
I would like to know, how you can extract pieces of text from the documents.
Example:
I have a ASPX page with codebehind in C# making the search in a table in SQL server that is full text indexed.
I make a search looking for the word "peace", than SQL server will take care about the search and return it to me the rows that match with that. But also I'd like to extract the 50 characters before and after where sql server found the word "peace" to show in the result page.
Does anyone has any idea how to work around it ?
Best regards.
Yannick
I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.
View 3 Replies View RelatedI have a data table in my project that has a "text" (not varchar) field in it. I am trying to load this field with little paragraphs of text for showing on my pages. How do I get the free text loaded into the table? The database explorer and all the data grid controls cut the text off at one line. There doesn't seem to be any way to get multiline text into this table.
View 2 Replies View RelatedI have this simple full text search query that works perfectly on my own computer using sql server 2005 express, however, on the production server(shared hosting)when I added the first 50+ rows, the full text search works perfect, but as the number of rows increases, the full text search can only see the first50+ rows, but not the new ones. Is there any quick solution for this or it's just a common mistake for developers for not properly indexed columns?Is there a way to re-indexed all rows without loosing data on the live server? search query: SELECT TOP 50 *FROM li_BookmarksWHERE FREETEXT(Keywords,@Keywords)
View 2 Replies View RelatedHello there!
I installed and configured SQL Server 2005 reporting services. When I try to connect to it using SQL Server Management Studio, I get the following error:
Client found response content type of "text/html", but expected "text/xml".
What should be done to overcome this?
Does anyone have any idea about this?
Thanks in advance
Hemant
Hi,
In my VB .Net application , when I am trying to fire a SSRS report on my local machine . Its giving the error ".Client found response content type of 'text/html', but expected 'text/xml'."
The request failed with the error message:
--
<html><head><title>Error</title></head><body>The Local Security Authority cannot be contacted
</body></html>
--.
the error is being thrown at the code line...
reportviewer.serverReport.getParameters()
I have reportserver as
https://ReportServerforApplication.abc.com/abc_ReportServer
I have admin rights on my machine but still the problem persists. Am i missing some user group?
Could someone Please help me with this.
Thanks In Advance
Gaurav
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
I get this error:
The data types text and text are incompatible in the equal to operator.
when trying to execute this query
SELECT id FROM users WHERE username=@userName
Any Ideas?
I have text data files from a third party and they use comma as field delimiters and enclose the text for each column in double-quotes. Not a problem for most of the data files until they start sending files where there is " within the column values. SSIS package fails with the error:
The column delimiter for column "Column 1" was not found.
Any ideas on how to resolve this issue will be greatly appreciated.Thankspcp
Hi,
Im a programmer for an university webportal which uses php and msssql.
When an user creates a new entry and his text is too long the entry is cut short and weird characters appear at the end of the entry.
For example:
http://www.ttz.uni-magdeburg.de/scripts/test-messedb/php/index.php?option=show_presse&funktion=presse_show_mitteilung&id=333
How can I set the text limit to unlimited?
Could it be something else?
Is there a way of splitting an entry to several text fields automatically?
Thanks in advance for any help you can give me,
Chris
Hi,
I am getting these errors from Report Manager. It seems that every time after the server has been idling for about 15 minutes, I would get this error message. I can click on other links and I am able to continue to use Report Manager.
Is there a timeout setting to help me resolve this or is this a bug with Report Manager? I just insitalled SP2 on the SQL Server and the Reporting Service.
Error message on browser:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <html> <head> <title> SQL Server Reporting Services </title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.3042.00" /> <meta name="HTTP Status" content="500" /> <meta name="ProductLocaleID" content="9" /> <meta name="CountryLocaleID" content="1033" /> <style> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black} H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt} LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline} .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray} A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none} A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLORFF3300; TEXT-DECORATION:underline} A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none} A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; colorFF3300; TEXT-DECORATION:underline} </style> </head><body bgcolor="white"> <h1> Reporting Services Error<hr width="100%" size="1" color="silver" /> </h1><ul> <li>An internal error occurred on the report server. See the error log for more details. (rsInternalError) <a href="http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsInternalError&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.3042.00" target="_blank">Get Online Help</a></li><ul> <li>For more information about this error navigate to the report server on the local server machine, or enable remote errors</li> </ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span> </ul> </body> --.
Any ideas?
Thanks,
-waslam
I have a parameter value as shown below and this is dynamic and can growÂ
Example : 101-NY, 102-CA, 165-GA
116-NY, 258-NJ, 254-PA, 245-DC, 298-AL
How do I get the values in the below format
NY,CA,GA Â --- each state to be followed with comma and the next state
NY,NJ,PA,DC,AL Â --- each state to be followed with comma and the next state
correct query that will fetch  only state names and not the numbers.
Hello,
I know this question has been asked here before, but it was ridiculously answered : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=98764&SiteID=1
I have a text object on my crystal report, to which I programmatically want to assign text in CS. It all works fine, except that if I have html tags in there, the text doesn't get formatted, it just displays the html tags as text as well.
Now in thread linked above, the answer was: right click the text object, and format the text object, hit paragraph text and select text interpretation to HTML. The only problem is, that for a text object, it doesn't have the text interpretation option. It's only an option for a formula field. It says so in the tech support link posted inside the post itself!!!
So if you have any idea how I can do this, please someone help me out. I am new to crystal, and there's not much helpful info on it online. I've searched for this for hours, and no solution.
Thanks
In SQL 2005 if i was trying to insert some data with a text qualifier inside a text qualified field, it would work, for example:
"Name","ID ","Location","","Comany",""House Name" Road",
In SQL 2012, this fails with the error message, cannot find the text qualifer for field.
To get around this, we are having to import the data into a Dirty Data column of aTEMP table, ID, Dirty Data, Clean data - perform multiple updates and change the text qualifier and ensure they are only changed in the right places so we can keep the ". In this example, we changed the text qualifier to PIPES.
After these updates, we then export the data from CLEAN data back out to CSV, then reimport it into the origional destination table with a new text qualifer.
Hi,I have setup a report model and am ready to deploy it for the first time. I have had no issues deploying my report definitions so presumably this should be alright.However, trying to deploy it gives this error:
TITLE: Microsoft Semantic Model Designer
------------------------------
A connection could not be made to the report server http://localhost/ReportServer.
------------------------------
ADDITIONAL INFORMATION:
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>
SQL Server Reporting Services
</title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.1399.00" />
<meta name="HTTP Status" content="500" />
<meta name="ProductLocaleID" content="9" />
<meta name="CountryLocaleID" content="1033" />
<meta name="StackTrace" content=" at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load()
at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Construct(String configFileName)
at Microsoft.ReportingServices.Diagnostics.RSConfiguration..ctor(String configFileName, String location)
at Microsoft.ReportingServices.Diagnostics.RSConfigurationManager..ctor(String configFileName, String configLocation)
at Microsoft.ReportingServices.Library.Global.get_ConfigurationManager()
at Microsoft.ReportingServices.WebServer.Global.StartApp()
at Microsoft.ReportingServices.WebServer.Global.Application_BeginRequest(Object sender, EventArgs e)" />
<style>
BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black}
H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt}
LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline}
.ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray}
A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none}
A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLORFF3300; TEXT-DECORATION:underline}
A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR3366CC; TEXT-DECORATION:none}
A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; colorFF3300; TEXT-DECORATION:underline}
</style>
</head><body bgcolor="white">
<h1>
Reporting Services Error<hr width="100%" size="1" color="silver" />
</h1><ul>
<li>The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) </li><ul>
<li>Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerRSReportServer.config' is denied.</li>
</ul>
</ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span>
</body>
</html>
--. (Microsoft.ReportingServices.SemanticQueryDesign)
I'd greatly appreciate any insight you could give me into fixing this problem.ThanksJohn
Hi,I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it.
I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns)
For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal.
<ProductMergeAudit> <before> <table name="table1" description="Test Desc"> <product id="OldVal"> </table> </before> <after> <table name="table1" description="Test Desc"> <product id="NewVal"> </table> </after></ProductMergeAudit>
I am using the following plumbing code to search a database column for a keyword. I can't use full-test indexing so I came up w/ this work around. But It has many flaws so I'm looking for a better way. Thx in advance.
'Open sql connection
SqlConnection1.Open()
Dim datareader2 As SqlClient.SqlDataReader
datareader2 = cmdFindRowsWithKeyword.ExecuteReader
Dim strMsg As String
Dim intRowToFlag As Integer
Dim strRowsToGet As String
Dim strKeywordAsTyped As String
Dim strKeywordAllCaps As String
Dim strKeywordAllLower As String
Dim strKeywordFirstLetterCap As String
Dim FirstLetter As String
While datareader2.Read
intRowToFlag = datareader2(0).ToString
strMsg = datareader2(1).ToString
'Assign keyword as typed to variable
strKeywordAsTyped = txtSearchFor.Text
'Assign keyword as typed to variable then convert it to all uppercase
strKeywordAllCaps = txtSearchFor.Text
strKeywordAllCaps = strKeywordAllCaps.ToUpper
'Assign keyword as typed to variable then convert it to all lowercase
strKeywordAllLower = txtSearchFor.Text
strKeywordAllLower = strKeywordAllLower.ToLower
'Assign keyword as typed to variable then convert it so just the first letter is in uppercase
strKeywordFirstLetterCap = txtSearchFor.Text
FirstLetter = strKeywordFirstLetterCap.Chars(0)
FirstLetter = FirstLetter.ToUpper
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Remove(0, 1)
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Insert(0, FirstLetter)
'If the string contains the keyword as typed in all caps all lowercase or w/ the 1st letter in caps then flag that row.
If strMsg.IndexOf(strKeywordAsTyped) <> -1 Or strMsg.IndexOf(strKeywordAllCaps) <> -1 Or strMsg.IndexOf(strKeywordAllLower) <> -1 Or strMsg.IndexOf(strKeywordFirstLetterCap) <> -1 Then
cmdFlagRowsWithKeyword.Parameters("@recid").Value = intRowToFlag
SqlConnection2.Open()
Dim datareader3 As SqlClient.SqlDataReader
datareader3 = cmdFlagRowsWithKeyword.ExecuteReader
datareader3.Close()
SqlConnection2.Close()
End If
End While
datareader2.Close()
I have a SQL SERVER database which has Articles Table. This table
contains "Description" field which is of type "text". I am trying to
insert 800- 1000 words of data into this field. This data also contains
code snippets. I dont know for some reason it only inserts one or two
lines and thats it. No error is being thrown. I am using multiline
textbox to enter the data into the database. any ideas
It displays something like this:
test 1
By AzamSharp
Creating XML Men // This is very long text. Actually its the whole article but it only displays three words
any ideas !
Thanks,
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
View 9 Replies View RelatedI have a SQL Server database. The data from a table is populated in the table and can do a regular display query on a record without issue.
Problem is when I pull the data into a form the data doesn't show up in some form fields for editing.
I am building a backend for the manager to make updates and changes and this is vital. Does anyone know if it has something to do with a database setting or has had a similar issue in the past?
The reason I think its a database setting is becuase the same table converted into MS Access has no problem populating the text boxs and text areas.
Your help is much needed and appreciated.
Thanks.
Hi,I have a problem to insert(update) a long text (more than 64K) intoSQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.MSDN says: "When the ntext, text, and image data values get larger,however, they must be handled on a block-by-block basis. BothTransact-SQL and the database APIs contain functions that allow applications towork with ntext, text, and image data block by block." Could somebodygive me an example how to do this, please.Thank you
View 2 Replies View RelatedHi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
Hi All
Iam trying to Get a text field value i wrote this code
DECLARE @ptrval varbinary(16)
DECLARE @length bigint
SELECT @ptrval = TEXTPTR(Template), @length = LEN(Template)
FROM #TEMPLATE
READTEXT Template.#TEMPLATE @ptrval 0 @length
but i need to put the result into a text var
is that possible or not and if it possible any one could help me with that
We have a text field which is being written to from a java app through JDBC-ODBC. But the data seems to be trucated in the DB. How do we store all the data in this field (the text being stored can be quite large) without it being truncated?
View 1 Replies View RelatedHi everyone,
I'm extremely new to SQL so be nice
I am attempting to write a script to add onto the end of a text field the words " -- Disposed " (About 60 rows worth).
The field is a TEXT field, so unlike a varchar field I can't just use Update as shown below.
Code:
Update AR_Primary_asset
Set AR_Primary_asset.description = AR_Primary_asset.description + ' -- Disposed'
Where AR_Primary_Asset.ASSET_REF in ('1','2','4')
I found on the Mircosoft pages about UPDATETEXT, but this only seem to work to update one row (In the case below Asset_ref = 3, was the only row effected) .
Code:
DECLARE @Dispose binary(16)
SELECT @Dispose = TEXTPTR(DESCRIPTION)
FROM AR_PRIMARY_ASSET
WHERE AR_Primary_Asset.ASSET_REF in ('1','2','3')
UPDATETEXT AR_PRIMARY_ASSET.DESCRIPTION @ptrval null null ' -- Disposed'
So i wrapped it into a cursor, this worked on my test SQL server which runs SQL2005.
Code:
DECLARE @Dispose varbinary(16)
DECLARE cursor1 CURSOR FOR
SELECT TEXTPTR(DESCRIPTION)
FROM AR_Primary_Asset
Where AR_Primary_Asset.ASSET_REF in('1','2','3')
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @Dispose
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATETEXT AR_Primary_Asset.DESCRIPTION @Dispose NULL NULL ' -- Disposed'
FETCH NEXT FROM cursor1
INTO @Dispose
END
CLOSE cursor1
DEALLOCATE cursor1
But when it was run on our SQL2000 server it gave the following error message
Quote: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
I've never used vars, cursors, updatetext or even text fields before. So maybe I am going about it totally the wrong way.
Is anyone able to tell me a better way to write this? or how to make it compatible to SQL2000?