hi all
i have a filed in my database with name address which contains addresses.
i want to split address in multiple line .
LIke
addressline1
addressline2
addressline3
i.e
Robart Peter
4th Banglow road
MC city
can any one help me.
I have text in a description field which has a new line character which is causing the value from the column being displayed in 2 lines
'Receipt#
3 generated for the amount of $ 130.00'
How can replace the new line character with blank space ''.
Any suggestions and inputs would help
Thanks
Dear all, I wanted to write a select statement which display the letter A in the first line and B will be in the second line.
The below second statement achieve my goal. But the first one is not displaying as i expected. How can i write the select statement to display the letters(A&B) in different line. Please let me know.
SELECT ('A' + CHAR (13) + 'B')
PRINT ( 'A' + CHAR (13) + 'B')
hai
my table structure is as follow
S.no Newstring
1 A001,AA001,A002
2 B001,BB001,CCC003
i want the result table to be as
S.no NewString
1 A001
AA001
A002
2 B001
BB001
CCC003
how can i do this.please help me.
thanks in advance
I have a need to have new line character in some data. I am trying to insert and retrieve the data with the new lines char in it, but it does work. I need the new line char in the data as I need to do some further processing whenever the new line starts.
Any ideas on how I can achieve this?
Thanks for the help.
This are the ways I have already tried, but does not work
create table mytest (col1 varchar(max))
insert into mytest values (' line1 ' + CHAR(13) + ' line2')
update mytest set col1 = (' line1 ' + CHAR(13) + CHAR(10) + ' line2')
update mytest set col1 = 'line1
line2
aa
line3 '
update user03.amitatest set col1 = 'line1 ' + CHAR(13) + CHAR(10) +
' line2 ' + CHAR(13) + CHAR(10) + 'aa ' + CHAR(13) + CHAR(10) + 'line3'
select col1 from mytest
The result set from the above select is -
line1 line2 aa line3
I am writing a string to a text file using the below code:
DECLARE @MyText nvarchar(500)
SET @MyText = 'type This is my text >> c:MyLog.txt'
exec master..xp_cmdshell @MyText
What I want to know is. how do I write multiple lines to MyLog.txt without having to call out xp_cmdshell each time I want a new line in my log? Do I use newline character in @MyText like SET @MyText = 'type This is my text line 1</n>This is line2 >> c:MyLog.txt'
Expected output:
type This is my text line 1
This is line2
Hello all,
Im looking for an efficient way to remove all end of line character from a string.
Is there a function to do that or to replace them with another character?
Not sure if this is the right forums to ask this question..
I have a website in which i have a text box that has multiline = true... So suppose if if the user enter absbsbcjd and then hits enter and then again enter jfkdjf in my database i been stored as abscbabc twoboxes (new line) and then jfdkf and then sometime later i am taking that plan name and sending it as a subject to an email but since it has a new line character in that field.. i get an error..
so how can escape the new line character..
thanks
Karen
Hi!
Thanks For your reply!
but this is very urgent please help!!!!!!!
I need one more help in this issue. what if i do not need any "
for e.g: i am trying to set conn string and varaible value
jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f "" + path + "" /Conn "" + Packconn + "" "" + connect + "" '",cconn);
i am getting some value like this :
CommandText "xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn "SE413695\AASQL2005.TestDB;" "Provider=SQLNCLI.1;Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;" '" string
I do not need the highlighted escape characters in it. what should i do??????
i need some thing like this :
xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx" /Conn SE413695AASQL2005.TestDB;"Provider=SQLNCLI.1;Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'
Thanks,
Jas
I have a stacked bar chart which i notice a vertical line char. how can i removed this vertical line? I already modified the properties under smart labels under calloutlineanchor, calloutlinestyle, calloutstyle to None but still getting this vertical lines. another things is the data label as you can see in the the first column the 1.8% should be place under the gray color but its already shown before the orange color.
View 3 Replies View RelatedHello,
Can anybody help me with this error , System.Data.SqlClient.SqlException. "Line 1: Incorrect Syntax near :'m' ."
I have a textbox named DESCRIPTION and am trying to insert the values entered by user in this textbox.
It works well if a user types " I am unable to " but it throws this error when a user types " I'm unable to "...because of I'm ..
How do i fix this?
My Query:
strSQL = "Insert into ABC(DESCRIPTION) values('" & strDescription & "')"
When i try below query in query analyser , am getting this error ....
Error 1: [Line 1: Incorrect syntax near 'm'.]
Error 2: [ Unclosed quotation mark before the character string '.]
update ABC set DESCRIPTION = 'I'm unable' WHERE ABC_ID = '142'
Thanks to reply.
I have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last
I have the following scenario, The contents of main file are like :
ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it
And I need the output in the following form:
ServerCentral=forum=is=the=best
so=be=on=it
The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.
I have SQL 2005 Server and Visual Studio 2005 and I am working in vb. I want to create a database which has photographs.
View 3 Replies View Related
Hi everyone, I have a small dilema here. I am running SSRS 2000, and I have a report that is grouped by Part Number. I have =Fields!Formula.Value < .1 so I can view only what is greater than .1 Well, my problem is that when I put it under the group Visibility properties, it does not pull all the data. and if i put it under detail Visibility. It does show me everything but any Part Number that is less than .1 shows just the part number but no detail. How can I make my report show all my data but without leaving data out????
Thanks ahead of time,
Abner
I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).
How do I select everything to the right of the dash when the number of characters to the left of the dash varies?
I have a stored procedure that takes various parameters and performs simple selects using these. A quick summary of the db structure this is working on would be :
lookup_table1
table1_id
description
lookup_table2
table2_id
description
This stored procedure attempts to select the given description from the given table and return the id, if the id isnt present it then inserts the new value and returns the new id.
Now I have this working, ish, but the problem that I have is that in the case of a new insert the procedure returns two datasets, an empty one (as the initial select returned no results) and another with the id of the newly inserted value.
This procedure as it stands right now
CREATE PROCEDURE aida_lookup
@lookup_table varchar(255),
@lookup_id_name varchar(255),
@lookup_description_col varchar(255),
@lookup_value varchar(255)
AS
declare @sql nvarchar(2048)
--
-- Check if the given lookup value exists in the give table / column
--
set @sql ='SELECT ' + @lookup_id_name + ' FROM ' + @lookup_table + ' WHERE ' + @lookup_description_col + ' = ''' + @lookup_value + ''''
EXECUTE sp_executesql @sql
if (@@rowcount = 0) goto new_value
if (@@error <> 0) goto on_error
return(1)
--
-- Insert new lookup value into the given table / column
--
new_value:
-- NEED TO CLEAR PREVIOUS NULL SELECT
begin transaction
set @sql = 'INSERT INTO ' + @lookup_table + ' (' + @lookup_description_col + ') VALUES (''' + @lookup_value + '''); SELECT SCOPE_IDENTITY()'
execute sp_executesql @sql
if (@@error <> 0) goto on_error
commit transaction
return(1)
--
-- Error handler
--
on_error:
rollback transaction
return (0)
GO
The problem being is that if the value is inserted as a new value I need to remove the empty dataset so that regardless of how the procedure is run, it will always return the id at position row 0 column 0.
My attempted solution was to declare a @temp var and place the return value of sp_executesql into this, and if it wasnt null then return it, and if it was then proceed to insert, e.g.
declare @temp int
...
SET @temp = EXECUTE sp_executesql @SQL
if (@@temp = null) goto new_value
SELECT @temp
return(1)
Obviously this doesnt work, so I am open to suggestions. To be completly honest I have run out of hair to rip out and I am sure this can probably be done in a much more elegant fashion, so any help will be greatly appreciated
Do anyone know how I can automatically place a value eg. €śNOTHING€? in a database column when there is no or has a NULL value. Thanks.
View 2 Replies View RelatedHi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
View 3 Replies View RelatedHi,
I am using Sql Server 2005.
I want to place a LOCK, insert something in the table then return back to the code (with the ID of the inserted item) and then call FUNCTION_A and then based on the outcome of the function I either want to
I want to keep the inserted item and release the lock or
I want to delete the inserted item and release the lock
Any suggestion is greatly appreciated.
Regards
naimulah
Our development server is limited on RAM and I have been asked to increase Tempdb. I made the decision to put it back on disk; something I have done dozens of times before. I have used the Enterprise Manager`s GUI interface and I have manually typed in the SQL using ISQL/w, both ways have resulted in the same error message.
Microsoft SQL-DMO
Error 5016: [SQL Server] Incorrect database name or device name(s).
I have dropped the old and created new database devices (with different names) and I still receive the error message. I have even tried rebooting with Tempdb back in RAM and on disk 1t 2MB (which works). And yes, I HAVE checked my typing.
I would GREATLY appreciate any ideas on what may be wrong. The system is 6.5 with SP4 on Win NT with SP3. I inherited the system, so I am not certain what the hisory is.
Thanks,
Mike Gaudet
Visages, Inc.
Hi, Gurus, I am running SQL-Server 2000 on SCSIIs with Raid-5. Three disks. I see only one Logical Disk with 3 partitions. Since it's recommended to have Log files seperate to Data files physicially, currently both exist in the D-Drive. How can it be accomplished?
Another question for ur kind suggestion. I have a merge-repl setup. Publisher and distributor at the same machine. One subscriber over the internet. Replication works through VPN rather that FTP. Whenever i apply SNAPSHOT it takes more that 12 hrs to replicate the 2 GB DB. Although VPN is more secured but the tunneling makes the replication rather slow i guess. Will there be a major difference if i open the default port of ftp on the ISA and change the subscriber to get the SNAPSHOT as Anonymous Subsciption. I mean What's better, Security VS Performance. During the SNAPSHOT the Web-users running on the Subscriber wait for the latest data which is very much annoying.
Another question please, I wanna replace the current publisher-DB machine with a new Hardware-Machine. I don't wanna lose Replication and re-running the SNAPSHOT. Is it possible to retain the publication by restoring the Master, Publication and Distributor respectively on the new machine. The new machine will have the same Operating system name and configuration.
Regards!!
I'm generating emails using sp_send_dbmail. Everything works perfectly except for one thing. In the body of the email I need to show a link to a web page (eg http://myweb/login.aspx).
The problem is that the received email shows the "link" as plain text, ie it is not a clickable link. I've tried adding char(13) (and char(10) and both) after the link text but that doesn't help.
Is there a way to make the link text a real link when received by Outlook? (All recipients will be using Outlook if that helps).
Thanks,
John
I have created my packages and i want them to place them on the server.Do i need to place the entire project of dts packages on the server or is there any option to place executables...if so please explain....
And to run these packages on the server do i need to set them as new job at sql server agent or is there any other way i need to run on the server.
I want then to run whenever the text file gets updated is it possible to set anything for my packages to run as and when the text file gets updated..
Please help me with all my questions
Thanks in advance..
I have been requested to add the sum of an interger field to the table header. I have the sum in the footer (which is very easy to do), but I cannot get the sum to appear in the table header.
I then set-up the stored procedure to run the sum, and place it into a dummy field. I still cannot add this field to the table header. Instead of printing the data for the dummy field (the correct total), it instead prints the actual field name on the report.
Is there anyway to place a sum in a table header on a SQL Server Report?
Let me know.
Thank you,
T.J.
Hi,
After exporting the report the column headers in the rreport should not scroll, it has to fix with report headers. If i move the column headers to report header the columns are merging in excel, some columns are hiding.
For ex: I have 10 columns, it has to take from A to K but it is taking A to P. one column is occupying two columns. how to avoid this merging and how to place the column headers in the report header.
Thanks
Dinesh
I have a define formula that is SUM(Loan Loan Amount[2]) and this formula has a addition filter off of it.
The report is pulling off of calander months months so if in some of the fields it has a dollar amount but in others it is blank. Trying to write formula that if there is no amount then place zero otherwise put the $ amount.
thanks
I'd like to return the left-most character from an 8 character string & the third from the left character too.
Like this ABC00123 returns AC
$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());
echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){
[Code] ....
We have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.
View 1 Replies View RelatedI have a line graph which shows positive and negative values. Is it possible to have the line one color when its negative and another when its positive?
kam