Error Concatenating String

Jul 20, 2005

I am trying to create a job that, as one of its steps, will kick off a
DTS package. As part of the command parameter, I need to concat a
system variable (@@SERVERNAME) to a constant string. I am receiving an
error about incorrect syntax near the +.

Here is the code for the job step.

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep@job_id = @JobID,
@step_id = 1,
@step_name = N'Import OCC Series Data',
@command = N'DTSRun /F
D:DatabasesScriptsDTSImportOCCSeriesData.dts /A DbName:8=' +
@@SERVERNAME,
@database_name = N'',
@server = N'',
@database_user_name = N'',
@subsystem = N'CmdExec',
@cmdexec_success_code = 0,
@flags = 2,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 3,
@on_fail_step_id = 0,
@on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

If I just try SELECT N'DTSRun /F
D:DatabasesScriptsDTSImportOCCSeriesData.dts /A DbName:8=' +
@@SERVERNAME, everything works fine. I even tried declaring a local
variable named @command and setting it in the select statement, but no
dice.

View 2 Replies


ADVERTISEMENT

Concatenating String Column

Aug 13, 2003

Hi All,

I am trying to write a select statement which will concatenate all values of a string column and provide me with a result set containing just one row of data containing a concatenation of all values.

For eg:

column1
abc
def
hij
klm
nop

is it possible to write a select statement which would return

result
abcdefghijklmnop

as a result?

TIA
Ketan

View 6 Replies View Related

Concatenating Dynamic String In SP

Mar 12, 2008

Hey guys, I 'm coding my very first stored procedure as accessed by a
.NET application. My input parameter is a dynamically built string. I need to concatenate to a sql query within the SP. I've tried using '+' as the concat. character but it doesn't work.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[tmptable_query] (@condition_cl varchar(100)) as select * from temp_table + @condition_cl

Help would be appreciated. Thank you.

View 5 Replies View Related

T-SQL (SS2K8) :: Concatenating String Without Cursor

Mar 27, 2015

Each patient has multiple diagnoses. Is it possible to concatinate all of them in one without using a cursor?

I attach a small sample - just 3 patient (identified by VisitGUID) with the list on the left, the desired result on the right.

View 8 Replies View Related

Concatenating Filename And Variable/string

Mar 31, 2008

I'm trying to write a stored procedure in SQL that will make a copy of a file and then append the current date to the end of the copy's filename. I'm storing the date in a variable called @currentdate which is defined as:

declare
@currentdate varchar(10)
set @currentdate='' + 'select datepart(month,getdate())' + '-' + 'select datepart(day,getdate())' + '-' + 'select datepart(year,getdate())'

Here is the SQL code I'm using:

exec xp_cmdshell 'copy "C:DevelopmentParticipant Limits ReportParticipant Limits Report template.xls" "C:DevelopmentParticipant Limits ReportParticipant Limits Report ' + @currentdate + '.xls"'
GO

The resulting file should have a filename something like "Participant Limits Report 3-31-2008". I get an "Incorrect syntax near '+'." error.

View 20 Replies View Related

Problem Concatenating Column Values Into String...

Apr 5, 2006

I have a customer who has recently migrated their SQL server to a newserver. In doing so, a portion of a stored procedure has stoppedworking. The code snippet is below:declare @Prefixes varchar(8000),declare @StationID int-- ...select @Prefixes = ''select @Prefixes = @Prefixes + Prefix + '|||'from Devicewhere Station_ID = @StationIDEssentially, we are trying to triple-pipe delimit all the deviceprefixes located at a specified station. This code has workedflawlessly for the last 10 months, but when the database was restoredon the new server, @Prefixes only contains the prefix for the lastdevice.Is there a server, database, or connection option that permits this towork that I am not aware of? Why would this work on the old server andnot on the new? (BTW - both servers are running SQL 2000 StandardSP4).Thanks!

View 6 Replies View Related

Converting Timestamp To Varchar Or Concatenating It With A String

Sep 20, 2007

Hello,

I apologise if this question has been asked before but I have searched forums and the web and have not found a solution. I am current creating a script that has a cursor that builds a sql statement to be executed e.g.

--code within cursor

SELECT '
DECLARE @Result INT
EXEC @Result = DELETE_DOCUMENT
@DocumentID = ' + STR(DocumentID) + ',
@TimeStamp =' + CAST([Timestamp] as varchar) + ',

-- CHECK RESULT AND STATUS
-- IF OK LOG IN META_BATCH ELSE LOG ERROR' AS SQL
FROM Document



The problem I am having is trying to join the timestamp column into the sql string. I have tried to cast the time stamp to a varchar but I end up with the following output for the timestamp column values

T
T€‘
T­
xnÞ
T!
T"
T#
T$
T%
T&
T'
T(
T)
T*
T+
T,

instead of


0x0000000013540F1C
0x0000000013540F1E
0x0000000013540F1F
0x0000000013786EDE
0x0000000013540F21
0x0000000013540F22
0x0000000013540F23
0x0000000013540F24
0x0000000013540F25
0x0000000013540F26
0x0000000013540F27
0x0000000013540F28
0x0000000013540F29
0x0000000013540F2A
0x0000000013540F2B
0x0000000013540F2C


which would not allow my delete script to work correctly. So I would really appreciate some advice to a pointer to where I might find out how to convert the timestamp.

Thanks
Sam

View 3 Replies View Related

ERROR:Syntax Error Converting Datetime From Character String. With Stored Procedure

Jul 12, 2007

Hi All,





i have migrated a DTS package wherein it consists of SQL task.

this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.



But the SP can executed in the client server. can any body help in this regard.





Thanks in advance,

Anand

View 4 Replies View Related

Error: 26 - Error Locating Server/Instance Specified - (My Hosting Company Says It's In My Connection String) NOT!

May 24, 2007

Hi All...



I am trying to move my site (incomplete) for testing and I am finding that the Db connection process to be

the most difficult, well back to my problem. I can't seem to get past this error and am frustrated with overall

connectivity problems. Please help!!!



===================================================================================


An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:





An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:





[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121
System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105
System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42
System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83
System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160
System.Web.UI.WebControls.Login.AttemptLogin() +105
System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102





Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210





Now you all must admit that, if your somewhat new to this databasing, It's a ***.



The source for my application is Visual Web Designer 2005 Express

It is an ASP.Net driven site with membership roles and all the good stuff.

the latest theory is that I need to change the provider config in the web.config file from AspNetSqlProvider

to something else. Most of my complications and frustrations come from GoDaddy and no access to the

shared server (understandibly).



I hope I gave enough info for help and not too much..



Dave



View 4 Replies View Related

Stored Proc Error: Error Converting Character String To Smalldatetime Data Type

Feb 12, 2005

I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.

Error from SQL Debug:
---
Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.
---

Error from page execution:
---
Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.

Source Error:

Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")
Line 78:
Line 79: cmd.ExecuteNonQuery()
---


Below is what I currently have for my stored procedure and the pertinent code from the page itself.

Stored Procedure:
---
CREATE PROCEDURE dbo.AdminAddUser( @username varchar(100),
@password varchar(100),
@email varchar(100),
@acct_type varchar(100),
@realname varchar(100),
@billname varchar(100),
@addr1 varchar(100),
@addr2 varchar(100),
@city varchar(100),
@state varchar(100),
@country varchar(100),
@zip varchar(100),
@memo varchar(100) )
AS
BEGIN TRAN

--
-- Returns 1 if successful
-- 2 if username already exists
-- 0 if there was an error adding the user
--

SET NOCOUNT ON
DECLARE @error int, @rowcount int

--
-- Make sure that there isn't already a user with this username
--

SELECT userID
FROM users
WHERE username=@username

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount > 0 BEGIN
ROLLBACK TRAN
RETURN 2
END

--
-- Set expiration date
--

DECLARE @expDate AS smalldatetime

IF @acct_type = "new_1yr" BEGIN
SET @expDate = DATEADD( yyyy, 1, GETDATE() )
END

IF @acct_type = "new_life" BEGIN
SET @expDate = DATEADD( yyyy, 40, GETDATE() )
END

DECLARE @paidCopies AS decimal
SET @paidCopies = 5


--
-- Add this user to the database
--

IF @acct_type <> "new" BEGIN

INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userPaidCopies, userExpirationDate, userLastPaidDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@paidCopies, @expDate, GETDATE(), @addr1, @addr2, @city, @state, @country, @zip, @memo )

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END

END

IF @acct_type = "new" BEGIN

INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@addr1, @addr2, @city, @state, @country, @zip, @memo )

SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT

IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END

END



COMMIT TRAN
RETURN 1
GO
---


Page Code:
---
Sub AddUser(Sender as Object, e as EventArgs)

Dim db_conn_str As String
Dim db_conn As OleDbConnection
Dim resultAs Int32
Dim cmdAs OleDbCommand


db_conn_str = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=xxxxx; User ID=xxxxx; PWD=xxxxx;"
db_conn = New OleDbConnection( db_conn_str )
db_conn.Open()

cmd = new OleDbCommand( "AdminAddUser", db_conn )
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add( "result", OleDbType.Integer ).Direction = ParameterDirection.ReturnValue

cmd.Parameters.Add( "@username", OleDbType.VarChar, 100 ).Value = Request.Form("userName")
cmd.Parameters.Add( "@password", OleDbType.VarChar, 100 ).Value = Request.Form("userPass")
cmd.Parameters.Add( "@email", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@acct_type", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@realname", OleDbType.VarChar, 100 ).Value = Request.Form("userRealName")
cmd.Parameters.Add( "@billname", OleDbType.VarChar, 100 ).Value = Request.Form("userBname")
cmd.Parameters.Add( "@addr1", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr1")
cmd.Parameters.Add( "@addr2", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr2")
cmd.Parameters.Add( "@city", OleDbType.VarChar, 100 ).Value = Request.Form("userCity")
cmd.Parameters.Add( "@state", OleDbType.VarChar, 100 ).Value = Request.Form("userState")
cmd.Parameters.Add( "@country", OleDbType.VarChar, 100 ).Value = Request.Form("userCountry")
cmd.Parameters.Add( "@memo", OleDbType.VarChar, 100 ).Value = Request.Form("userMemo")
cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")

cmd.ExecuteNonQuery()

(...)

---

View 1 Replies View Related

Why Am I Getting An Error Runtime Error Conversion String Type Pr_h_reqby To Int

Jan 17, 2007

here is my code the falue of pr_h_reqby is "Test" 
Dim strconn As New SqlConnection(connstring)
Dim myReqdata As New DataSet
Dim mycommand As SqlDataAdapter
Dim sqlstr As String = "select pr_H_reqby from tbl_pr_header where pr_h_recid = " & recid & ""
mycommand = New SqlDataAdapter(sqlstr, strconn)
mycommand.Fill(myReqdata, "mydata")
If myReqdata.Tables(0).Rows.Count > 0 Then
'lblReqID.Text = myReqdata.Tables(0).Rows("reqid").ToString
lblNameVal.Text = myReqdata.Tables("mydata").Rows("pr_H_reqby").ToString()
lblEmailVal.Text = myReqdata.Tables("mydata").Rows("pr_h_reqemail").ToString()
lblReqDateVal.Text = myReqdata.Tables("mydata").Rows("pr_h_reqdate").ToString()
lblneedval.Text = myReqdata.Tables("mydata").Rows("pr_h_needdt").ToString()
lblDeptval.Text = myReqdata.Tables("mydata").Rows("pr_h_dept").ToString()
txtbxReqDesc.Text = myReqdata.Tables("mydata").Rows("pr_h_projdesc").ToString()
End If

View 1 Replies View Related

Error = -2147467259 (80004005) Error String: Timeout Expired

May 5, 2003

Hi everybody,
I do custom log shipping over network like

step 1 backup log to C:..
step 2 zip log file c:... zip
step 3 copy zip to \sm01
estorelogs...
step 4 unzip file
step 5 restore log with stand by

Few times a month it fails with

Error = -2147467259 (80004005) Error string: Timeout expired Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: Timeout expired Error source: Microsoft OLE DB Provider for SQL Server

Any idea ,what might cause it ?

Thank you
Alex

View 3 Replies View Related

Error 241: Syntax Error Converting Datetime From Character String

Jan 7, 2004

Hi All, can someone help me,
i've created a stored procedure to make a report by calling it from a website.
I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me.
The code i use is:


CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime
as
set dateformat dmy
go
declare @startdate as varchar
declare @enddate as varchar

--print "query aan het uitvoeren"

select sum(moneyout) / sum(moneyin)*100 as cashbox
from dbo.total
where machineID = '@mID' and njdate between '@startdate' and '@enddate'
GO



Thanx in front
Cya

View 14 Replies View Related

Concatenating Help

Feb 28, 2008

Hello

First post here and am looking for some help with a issue I have with concatenation.

I am looking to concatenate two columns together into one new column...

ins_type + currency

I have searched for help onthis but for some reason nothing I do works...

Any ideas or suggestions?

Thanks

Nuno

SELECT
a.deal_tracking_num AS TemplateDealNum,
a.tran_num AS TemplateTranNum,
a.reference AS TemplateName,
a.ins_type,
a.currency,
a.trade_date AS TemplateCreated,
u.template_tran_num as Misc

FROM
ab_tran a, USER_restricted_templates u
WHERE
a.tran_type = 0 AND
a.asset_type = 2 AND
a.current_flag = 1 AND
a.toolset IN (33,36,15,16) AND
a.tran_status = 15 AND
a.tran_num *= u.template_tran_num

View 12 Replies View Related

Concatenating Fields

Aug 9, 2007

Hi,there's a method to concatenate fields in a WHERE clause?I've a parameter which represents a name and surname of a person; in the table I've two fields representing one the name and the other the surname. I'd like to do a "LIKE" comparison concatenating Name and Surname field and confronting with my parameter... Is it possible?

View 3 Replies View Related

Concatenating Fields

Aug 9, 2007

Hi,I've a table with two fields representing one Name and the other teh Surname of a persona. I've a to create a Stored Procedure with one input parameter that is a string containing Name and Surname (I don't know in waht order...)What I'd like to do is to concatenate teh fields Name and Surname and confronting with "LIKE" in the "WHERE" clause... something like this:Select Name, Surname FROM XXX where (Name + ' ' + Surname LIKE @parameter OR Surname + ' '+ Name LIKE @parameter).The problem is that I don't know neither if it is possible neither the correct syntax... 

View 1 Replies View Related

Concatenating Columns

Dec 13, 2007

Hi how would i concatenate columns from my database into one column, these are the columns im trying to concatenate;
SELECT  Companies.companyName, Companies.companyAddressLine1 , Companies.companyAddressLine2 as Company, Companies.companyAddressLine3, Companies.companyTown, Companies.companyPostcode, AS COMPANY
Thank you

View 7 Replies View Related

Concatenating Fields In SQL

Apr 11, 2008

I am using SQL server 2000 and trying to do a concatenation of firstname,middle-initial and lastname.
SELECT first_name+('mid_ini'= casewhen middle_init is null then ''when middle_init ='' then ''else middle_initend)+nsl_last_nameFROM tbl_names
How is it possible to use the CASE condition along with concatenating the fields. Is this possible?
I would like my result to be "Bob J Sam".
and if there is no middle initial it should be blank or when its null it should be blank.
I know you can separate and get the results with the CASE statement,but is this feasible when you want to concatenate?
Thank you

View 9 Replies View Related

Concatenating Columns

Mar 3, 2004

I have two columns of type integer that I want to concatenate. I also want to put a "/ " in between the columns.

I have tried doing it in the same way as I would for varchar columns but that doesn't seem to work for integers.

Thanks

Rupal

View 2 Replies View Related

Concatenating Sql Statements

Jan 30, 2002

I have a web application that has SQL7 as the back end. SQLServer and ORACLE have a feature that allows sql commands to be combined in one statement. Another words I am able to to

SELECT * FROM table_name WHERE id = 2 DROP TABLE other_table

If I type this in a query analyzer It will perform both commands. Is there a way to turn this 'feature' off.

The main reason I want to turn it off is so if a numerical value is sent as a url variable someone can't add the drop table statement or any other sql command to the value of the url variable and have it executed. We have added ample code to trap for this problem but I would like to also handle it at the database level.

Thanks,
Jeff

View 2 Replies View Related

Concatenating With A Case

Apr 21, 2005

I have a table with 1-4 20 character description fields which need to be concatenated into one value. The data is stored like below

patientid allergy1 allergy2 allergy3 allergy4
100
200 tylenol
300 tylenol bees

The way the data is stored there would never be a allergy3
with an allergy2 being null (the blanks are nulls)

I tried below but it doesn't like the case and concatenation. ANy better ideas

select patientid,allergy_cmt1 +
allergy_cmt2 =
case
when allergy_cmt2 = null then null
else allergy_cmt2 + ','
end
from cms_vw_ptdrugallergies2

View 3 Replies View Related

Concatenating Sql Statements

Jan 30, 2002

I have a web application that has SQL7 as the back end. SQLServer and ORACLE have a feature that allows sql commands to be combined in one statement. Another words I am able to to

SELECT * FROM table_name WHERE id = 2 DROP TABLE other_table

If I type this in a query analyzer It will perform both commands. Is there a way to turn this 'feature' off.

The main reason I want to turn it off is so if a numerical value is sent as a url variable someone can't add the drop table statement or any other sql command to the value of the url variable and have it executed. We have added ample code to trap for this problem but I would like to also handle it at the database level.

Thanks,
Jeff

View 2 Replies View Related

Grouping And Concatenating

Apr 6, 2004

i have a the following table:

CREATE TABLE [dbo].[tIndex] (
[indexID] [int] IDENTITY (1, 1) NOT NULL ,
[wordID] [int] NULL ,
[wordPos] [int] NULL ,
[paraID] [int] NULL
) ON [PRIMARY]
GO

for each wordID i have many paraID and for each wordID,paraID i have many wordPos

i will use the following convention:
paraID=p
wordID=w
wordPos=wp

i want to concatenate the columns to get the following format:

row1: w1 p1,NB1,wp1,wp2,w3... | p2,NB2,wp4,wp5,wp6... | ...
row2: w2 ...
row3: w3 ...


where NB1 is the number of wp having w1 and p1

Note: the length of a row may exceed 8000 chars

View 4 Replies View Related

Concatenating Using The IIF Statement

Apr 16, 2008

Hi Guys,

I am converting Access queries to SQL. One query has a string of IIF statements that can concatenate several strings into one field. For instance:

IIf([Hot Dogs]=-1,"* Wants a Hot Dog ","") &
IIf([Hamburger]=-1,"*Wants a Hamburger ","") &
IIf([mustard]=-1,"*add mustard ","") &
IIf([fries]=-1,"*Include Fries ","") &
IIf([Chips]=-1,"*Include Chips ","")

Consequently the output could be:
*Hamburger *add mustard *Include Chips

I know I can use the ‘Case When Then’ statement but how do I concatenate?

Thanx

View 4 Replies View Related

Concatenating In A Group By

Apr 22, 2008

Hi Guys, I am converting Access SQL to SQL Server and I have two problems:
First of all I can’t get the city, state and zip to concatenate. It will not take the "As Location" and it will not allow me to concatenate the fields in the Group By area.
Second I ‘m trying to replace a First() function with a Top() function but I’m not having much success. If it is not the first statement I get an error (Incorrect syntax near the keyword 'TOP'.)

Any ideas how I can accomplish what it is I want to do?

Thanx much,
Trudye

BEGIN

SELECT Date AS DateEntered,
LTRIM(RTrim([ResultsCustName])) AS CustName,
Timestamp, LTRIM(RTrim([ResultsPH])) AS Phone,
TOP (1) tblEscalationData.Explain_followup AS FirstOfExplain_followup,
[ResultsAddr1] AS Address1, [ResultsAddr2] AS Address2,
[ResultsCity] AS City, [ResultsST] AS [State], [ResultsZip] AS Zip,
ERROR
INTO zTmp_Training
FROM tblEscalationData
LEFT JOIN qry_IssuesPrint
ON tblEData.ResultsPKey = qry_IssuesPrint.ResultsPKey
LEFT JOIN LOOKUP_DATA
ON tblEData.DSLOAN = LOOKUP_DATA.DSLOAN
LEFT JOIN qry_IssuesPrintDef
ON tblEData.ResultsPKey = qry_IssuesPrintDef.ResultsPKey
INNER JOIN qry_CurrentStatus
ON tblEData.ResultsPKey = qry_CurrentStatus.ResultsPKey
LEFT JOIN tblErrors
ON Error = SUBID
WHERE tblEData.CSRReporting= 1 AND
qry_CurrentStatus.Date
Between @Start_Date And @End_Date
AND Source = @CallCtr
AND ERROR Is Not Null
ORDER BY LTrim(RTrim([ResultsCustName]))

END
--qry_CSRTraining (Group BY Clause)
BEGIN
SELECT DateEntered, LTrim(RTrim([CustName])), [Timestamp],
LTrim(RTrim([Phone])),
[Address1] + ' ' [Address2],
[City] + ' ' + [State] + ' ' + [Zip] As Location,
[Name], ERROR
FROM zTmp_Training
GROUP BY DateEntered, [CustName], [Timestamp],
[Phone], [Address1], [Address2], Location,
[Name], ERROR
END

View 2 Replies View Related

Concatenating Strings

Jan 27, 2008

I find myself between rock and hard place.

My requirement is to simply output a flat file from an OLE DB source containing three types of records.
The output records should look something like this:
Type 1 contains field1, field2, field3
Type 2 contains field1, field3, field6
Type3 contains field1, field4, field5

The source table contains all six fields.

If I simply map the input to output columns, I get all fields output in each record, which is not what I want.

So I run the source through a derived column transformation, inspecting the record type field and creating a new column containing the relevant fields concatenated together, e.g field1 + field 2 + field3 for the Type 1 record.

The problem is that the fields are now all scrunched together, i.e all the trailing spaces are being stripped out of each field during concatenation.

Is there any way to string these fields together while maintaining the original field length?

I'd like the output to look like
ABC FGH XYZ instead of
ABCFGHXYZ

View 7 Replies View Related

Concatenating Fields

Nov 9, 2007

I'm looking for ideas / options for concatenating several fields into one based on a sequence number. The sequence could go as high as 90 but I'm only interested in concatenating the first 3 sequences. I'd like to compare when seq is the same as the id, it's concatenated and placed in a virtual column.



Example:

table x:
column:attritbute
id:123
fname:xyz
lname:zmith
seq:1
address: 123 something
id:123
fname:abc
lname:zmith
address: 123 something
seq:2

Query:
select *
from x
where id = 123

would return 2 records:

ID | Concat | address | fname | lname
123 | abc / xyz | 123 something | xyz | zmith
...

I would like it to return:
ID Concat address
123 | abc / xyz | 123 something ....

Thanks!

View 1 Replies View Related

DTS Error - Error String: The Parameter Is Incorrect.

Nov 28, 2001

HELP! im running this sql and get the following error - If i manually start the dts package it is fine....also if i use DTSrun from my pc it also works ok..

USE master

EXEC xp_cmdshell 'DTSrun /S BTN_AH_TWB02NEW /U pkent /P clarke05 /N pbk'


output
DTSRun: Loading...

Error: -2147024809 (80070057); Provider Error: 0 (0)

Error string: The parameter is incorrect.

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts.hlp

Help context: 713




(9 row(s) affected)

View 2 Replies View Related

Concatenating Text In A Query?

Mar 7, 2014

I'm wanting to concatenate something that doesnt exist to an already existing field.

What I need to do is add a generic email address to every record in a data view So, what it would do is take the column that does exist and add @domain.com to another column that I would call username So rather than it just appearing as 911234 it would be a derived column saying 911234@domain.com

i've tried using +''+ but I get a space after the number.

View 2 Replies View Related

Concatenating Data From One Table To Another

Apr 3, 2015

I have a table and a view. Let's call the Table dbo.EmpPers and the view dbo.codes

EmpPers holds personal information and Codes holds all the codes for the company, naturally.

There is a field in Emppers called 'EthnicID'. It a number say for example 1, 2, 3 ect...

'Codes' has a field called CodDesc. It's a varchar for example White, Hispanic, African American. ect...

What is the best way to match up the description with the code? Say for all Persons who have have the Hispanic Code '2', I want to add the description to it in the select statement.

View 11 Replies View Related

Concatenating Strings From Different Rows

Nov 23, 2005

Hi,I have table which has the following values :ID SEQ Text1 1 A2 1 B3 2 C4 2 D5 2 E6 2 F7 3 GThe result should be :1 AB2 CDEF3 GCould somebody help me with this? I could use an cursor but the tablecould be large and i want a fast solution.Thanx in advance...Hennie

View 6 Replies View Related

Concatenating 2 Text Boxes

Feb 13, 2006

Hi,

How do we concate a normal text with an underlined text?

eg: I would like to concate :

textbox1 = my email address:

and

textbox2 = xxx@hotmail.com

into a new textbox3.

expected result: textbox3 : my email address:xxx@hotmail.com

Really appreciate your help.

Thanks

View 4 Replies View Related

Concatenating Text Fields?

Dec 18, 2007


Hi All,

I am trying to get at some text fields from an AS400(JBA) system. the problem i'm having is that for each new line of text on AS400 it creates a new row with a line number associated in the SQL table. When trying to export to excel this causes problems because i need the text in one cell not spread over 15/20 lines.

I need to create a query/function that will concatenate the text lines together. I'm fairly new to T-SQL so could do with some help.


Example:-
ID TxtLn Text








R262965
1
Text 1

R262965
2
Text 2

R262965
3
Text 3


What i'm aiming for is:-
ID ConcatText
R262965 Text1 Text2 Text3



Can anyone please help me or guide me towards a starting point.

Thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved