Replace Parameter Doesnt Work

Sep 15, 2006

I have the following in my commandtext but it doesnt seem to replace the LanguageColumnName variable:


       Dim cmd As New SqlCommand("SELECT '+@LanguageColumnName+' FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _
        "WHERE b.UserCode=@UserCode", MyConnection)
        cmd.Parameters.Add(New SqlParameter("@UserCode", UserCode))
        cmd.Parameters.Add(New SqlParameter("@LanguageColumnName", LanguageColumnName))

I have tried '+@LanguageColumnName+' and also just @LanguageColumnName but this variable isnt replaced for some reason.

The value of LanguageColumnName is "de"...the funny thing is that when I just type my command like the following it DOES work..:

SELECT de FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _
        "WHERE b.UserCode=@UserCode

What am I doing wrong?

View 1 Replies


ADVERTISEMENT

Stored Procedure Sort Parameter Doesnt Work

Jul 22, 2006

Hello, I am trying to make this.

CREATE PROCEDURE [dbo].[P_SEL_ALLPERSONAS]

@nmpersona int,

@sortorder varchar(20)



AS

BEGIN

select nmpersona, dsprimernombre, dssegundonombre,

dsprimerapellido, dssegundoapellido

from personas

order by @sortorder

END



But I got this error. Please help



Msg 1008, Level 16, State 1, Procedure P_SEL_ALLPERSONAS, Line 13

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

View 5 Replies View Related

Why Doesnt This Work?

Feb 22, 2006

i am new at T-SQL and am trying to delete entries in a column that are not integers. i have the following code. why does it not work.

T-SQL

select invoicenumber from [control register] where convert(int, invoicenumber) like '%'
if @@error > 0
begin
delete from [control register] where invoicenumber = invoicenumber
end
go

gives me the error in Query analyser:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '10-3-05' to a column of data type int.

@@error is supposed to pick up the error. why is it not.

please could you reply urgently at chris@captivedesign.co.za

thanks in advance



Chris Morton



View 8 Replies View Related

Can Someone Tell Me Why This Sql Statement Doesnt Work?

Jan 26, 2004

can someone tell me why this sql statement doesnt work?

SQL = "SELECT (Count(department_id) as 'totals' FROM nonconformance WHERE department_id = '7'),(Count(department_id) as 'totals2' FROM nonconformance WHERE department_id = '1') FROM nonconformance"


How do I fix it?

Thanks

View 2 Replies View Related

Case When Doesnt Work (for Me)

May 21, 2008

Can someone tell me why this thing in the end return Null?

declare @uz_id uniqueidentifier
set @uz_id=null
set @uz_id=
case @Uz_ID
when null then '00000000-0000-0000-0000-000000000000'
else @Uz_ID
end
select @uz_id

...it should return '00000000-0000-0000-0000-000000000000' in my opintion

P.S. When I say return i mean the value that is shown after 'select @uz_id' is executed:)

View 5 Replies View Related

Why Doesnt This Work............confused!?!?!

Mar 21, 2007

This is an insert statement i'm trying to run. I want it to only insert this recird if the corresponding EMPLOY_REF exists in the EMPLOYEE table. Heres my statement:


INSERT INTO SALHISTY(EMPLOY_REF, SALARY, SAL_REASON, SAL_DATE)
VALUES ('8971','175000.0000','ANNRV','2007-04-01 00:00:00.000')
WHERE '8971' IN (SELECT EMPLOY_REF FROM EMPLOYEE)


This is the error message i'm getting.

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.

Any help would be greatly appreciated. Thanks

View 14 Replies View Related

Why Doesnt My Query Work

Dec 6, 2007

select distinct
a.Patients,
b.Patients,
a.pct,
b.pct

from
(
select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] <= 18
group by [pct of res]
) as a right outer join
(select distinct[pct of res] from testing) as c on a.pct=c.[pct of res]and a.pct <> 'null' --is not null

(select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] >18
group by [pct of res]
) as a left outer join as b on c.[pct of res]=b.pct

View 6 Replies View Related

Simple Query Doesnt Work

Apr 23, 2008

I have my db in a pocket pc wm5.0, I just want to make a simple query
select * from table
where pk = '1'
but this doesnt work, if you tried any other field else than the primary key
it works... WHY???

would it be a problem with the sdf file? help please!!!

View 3 Replies View Related

A MergeJoin Using Two Fields Doesnt Work !

Jul 5, 2007

Hi,

I have an issue. When I am performing a Merge Join using two fields of type
DT_WSTR on both sides of the Join, the Dataviewer after the Join only shows the left side 2 key
fields having values populated but both fields from the right hand side as being NULL. The Dataviewers I put in before the join show both sides of the join fields being populated ( 2 sides x 2 fields ). If I remove one of the fields from the join it shows both fields from the left side and the right side of the join being populated . I am trying to knock out duplicate values from the insert using 'genuine' Nulls from the right hand side so I need both parts of the 'Key'. I have checked the adnvanced tab that ignores Case etc but has had no effect.

I am using the technique from here : http://www.sqlis.com/311.aspx

Is this an SSIS bug ??? It is driving me up the wall.

Help would be much appreciated please.

Thanks
Jon

View 1 Replies View Related

Changing In Sysaltfiles Doesnt Work

Jun 29, 2007

HI All,I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.1.)is there any systable still to be altered???2.) i have even tried alter database, doesnt work.3.)The master files have been taken from server1 (where tempdb points to d:data) to server2 ( where i need to point tempdb to point E:mssqldata). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path., too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path???

View 5 Replies View Related

Repeat Header Doesnt Work

Jun 18, 2007

hi there,

i've made a report containing a subreport. the subreport is in a table. the table headers should repeat. but if the subreport expands more than one site, the table header doesnt repeat.

okay. i could place the table header in report header, but in report header i cant use fields from a dateset.

thanks for any idea, tobi

View 1 Replies View Related

Vwd + Sse Deploying Database File Doesnt Work

Jan 17, 2006

hi, i hope this is the correct forum?i have developed a small asp.net 2.0 web site with vwd using sqlExpress which appears to just bean data file and a log file in my app_data directory. there are two databases, the users one that is created by the login manager and my own that i created to store data. this all works fine on my laptop. i have an xp-pro machine setup with IIS and the frontpage web extentions, i have also installed the .net framework v2.0 on it and done all the windows updates. i have given app_data read/write access for the aspnet and network service users.i used the copy site feature of vwd to install on the xp box and although the site seems to workwhen it needs to bind a gridview to my sqldatasource it gives an error Cannot open user default database. login failed.Loginfailed for user 'rlxpaspnet'Any ideas? i have googled and tried all sorts, but to no avail, so i throw my self on your mercy!Cheers,russ

View 5 Replies View Related

Auto Grow For DB Doesnt Work Anymore

Jul 3, 2001

Hello all!

I've a problem with my database. Till yesterday the option for Auto Grow of Database (10 %) was working very fine, but now it seems to be some problems with it. Finally I had to specify a restricted size for the database and then it again startd to give me some space in the database to write in. Ideally it should have worked automatically, isnt it ???

There is no problem with the space on the drive, I still have some 76 gb of free space there ...

Thanks in advance ...

Anjä

View 1 Replies View Related

Deploy From Visual Studio Doesnt Work

Apr 27, 2007

Hello, I created a new Rs server, but when I try to deploy from visual studio, it asks me for username and password multiple times and never deploys.



I am an administrator on the RS server, even on the report folders.

I can access via IE to the report manager.



View 17 Replies View Related

Rollback Doesnt Work In Begin Try ---catch

Nov 10, 2006



Hi ,



In the below procedure in emp table name column is not null.

so when i try to update null value it should rollback previous transactions and should not commit.

but it is updating the previous queries and raising error on 3rd query. not rolliing back. I need to rollback previous queries.

the xactstate i get here is 1.

Please help me with this.



ALTER PROCEDURE [dbo].[tran]

AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRANSACTION

declare @name varchar(50)

set @name = null

update dept set dname='ddd' where id=10

update emp set name='eee' where id=354

update emp set name=@name where id=354

COMMIT TRANSACTION



END TRY

BEGIN CATCH

IF (XACT_STATE())=-1 ROLLBACK TRANSACTION

END CATCH

END



venp---

Hi ,



View 6 Replies View Related

-T 1400 Doesnt Work On Named Instances

May 6, 2006

Hello, I tried this and it doesnt work, I just started up the service with -T flag on default instance but I havent been able to start it with -T flag 1400 on named instances.



How can I Know that the directory MSSQL.4 is the MIRROR instance??



C:Archivos de programaMicrosoft SQL ServerMSSQL.4MSSQLBinn>sqlservr.exe -T
1400 MIRROR

I tried LUCHOMIRROR, MSSQLSERVER$MIRROR

and It doesnt work



Thanks



Another question I have, in the documentation it says that mirroring is only for evaluation purposes and not for production environments, what does it suppose to mean? MSFT didnt test the mirroring feature enough? If its not suppoerted why its included..?



Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL).) Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.

View 1 Replies View Related

2 Tables = Redundant Data/ DISTINCT Doesnt Work

Apr 27, 2008

Hello everybody,

have following problem:

I need info from 2 Tables. from the Table 2 I just need 1 column. When i ask for this column the output I get is data repeating themselve many times.

Distinct, should give me unique data, but is doesnt....
the code:

SELECT DISTINCT FSenddate, FSupplyIDName, FSupplyerNumber,FBillNo,FSourceBillNo,FItemName,FItemModel,
FAuxQty,FAuxTaxPrice,FHeadSelfP0237
FROM vwICBill_26
WHERE FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()

This code just works in Table1 (vwICBill_26)

but with table 2 (vwICBill_1)

SELECT DISTINCT vwICBill_26.FSenddate,vwICBill_26.FSupplyIDName,
vwICBill_26.FSupplyerNumber,vwICBill_26.FBillNo,
vwICBill_26.FSourceBillNo,vwICBill_26.FItemName,
vwICBill_26.FItemModel,vwICBill_26.FAuxQty,
vwICBill_26.FAuxTaxPrice,vwICBill_26.FHeadSelfP0237,
vwICBill_1.FDate,vwICBill_1.FContractBillNo
FROM vwICBill_26,vwICBill_1
WHERE vwICBill_26.FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
AND vwICBill_1.FContractBillNo=vwICBill_26.FSourceBillNo

The last sentence is the problem
I want that it shows me the data that is not equal.
As soon as I implement the not equal it shows me the massive repeating data.
I mean even without the last sentence I get this data output.

All together, I want a clear database output without data repeating.
Any ideas how it may work without DISTINCT?


I think this problem is a typical amateure problem, but I would apreciate help!

View 2 Replies View Related

Linked Server Distributed Query Doesnt Work

Jun 21, 2006

 

Hi,

 The distributed query seems to work on the management studio of the server where I have linked the other server to but not accross the network on other management studio with the same impersonated logins. The error I get is.

 

OLE DB provider "SQLNCLI" for linked server "usbo-sql01" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18452, Level 14, State 1, Line 0

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

View 7 Replies View Related

Logging Of SSIS Package Doesnt Work When Executed As A Job

Mar 31, 2006

I am having the same problems as those in another post. SSIS package works fine when executed in BIDS and through execute package utility but it doesnt work when executed as a step in a job.

The other problem is that the logging also doesnt work when i try executing it as a job. So I have no clue about what to do without knowing what error it is. When I run the job it simply says the step has failed.

I have tried most of the solutions posted in other websites most of them to do with using proxies with credentials but havent hit a solution. I would love to get any input on what to do.

Thanks

View 6 Replies View Related

[SQL 2005] Backup Diff On MSDB Doesnt Work

Aug 1, 2007

Hello all,

I need help concerning a differential backup on a MSDB database.
I received this message when I tried to backup it


Log from Windows

Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 8/1/2007
Time: 2:03:37 PM
User: N/A
Computer: XXX
Description:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=9028. Thread=5928. Client. Instance=. VD=GlobalData Protector_(DEFAULT)_msdb_14_00_21.

Log from DataProtector

Normal] From: XXX "(DEFAULT)" Time: XXX
SQL statement:
BACKUP DATABASE [msdb] TO
VIRTUAL_DEVICE = "Data Protector_(DEFAULT)_msdb_06_00_14"
WITH NAME = 'Data Protector: 2007/08/01 0064', DIFFERENTIAL, BLOCKSIZE = 4096, MAXTRANSFERSIZE = 65536;
[Warning] From: XXX "(DEFAULT)" Time: XXX
Error has occurred while executing a SQL statement.
Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):bdb>
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a differential backup for database "msdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.'

Ive tried to do a full followed straight after by a diff but doent help.

Thank you for your help

View 7 Replies View Related

Setting ReportViewer URI And Path In Designer Works, C# Doesnt Work

Feb 26, 2008

in both vs2008 I added a ReportViewer control to my WinForm, intending to render an RS 2005 report remotely. In the design pane (Report Viewer Tasks Popup) I specify <server report>,
URL http://server name/reportserver$instance name
path /folder/report name

and everything is fine.

When I set these values in c# as

reportViewer1.ProcessingMode = ProcessingMode.Remote;

reportViewer1.ServerReport.ReportServerUrl = new Uri("http://server name/reportserver$instance name");

reportViewer1.ServerReport.ReportPath = "/folder/report name";

I started getting a msg saying "the source of the report definition has not been specified". If I hit refresh, the report renders. Before I started playing with the designer setting, and only had the c# stuff specified, I wouldnt even get the message, the control would appear with all RS buttons (eg page forward arrow etc) disabled.

Does anybody know why the message is appearing and how to get c#'s settings to work immediately like designer settings?

View 1 Replies View Related

Openrowset To Oracle Doesnt Work After Upgrading To SQL Server 2005

Jun 28, 2007

Hi,



I just upgraded to SQL server 2005 and some of my Openrowset to Oracle doesnt work. I found out that the issue occur with field with structure Numeric.

The issue is not solve even when I try to cast the field to varchar. sometimes it succeeded and sometimes I get error 7320 & 7321.

I'm using Oracle connector from SQL to Oracle.

I've 64 bit SQL Server.

Attach the code, the Numeric field is SCORE.



select *

FROM

OPENROWSET('ORAOLEDB.ORACLE',aaaa;bbbb;cccc,

'SELECT AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN,EMPLID, ACCOMPLISHMENT, MAJOR_CODE,

CAST(SCORE AS Varchar(250))

FROM SYSADM.PS_RTA_ACCOMPLISHM WHERE 1=2')



Anyone can advice on resolution?



Thanks,

Assaf

View 6 Replies View Related

SQL Server 2005 Installation Vista (+SP2), No Services, Doesnt Work

Nov 16, 2007



While installing SQL Server 2005 Enterprise Edition on Windows Vista Ultimate
the setup alerts me "there are known compatibility problems, please
install SP2 after this". So ok, all went nice and installed without error.
IIS 7 installed fully, so all green in setup display.
(But after installation i do not see any service which begins with SQL***...)
At the end, the setup wanted to start the administration Tool.
Ok, but my name was not listed, so i could not add me to the permitted users.
I can not use any tool, because i have no rights.

Some Ideas?

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services-Clienttools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16546
Microsoft .NET Framework 2.0.50727.312
Betriebssystem 6.0.6000


thanks Frank

View 6 Replies View Related

Sending An HTML Mail Message With The Script Task DOESNT WORK 4 ME

Oct 3, 2006

I set up the "Send Email Task" succesfully with "SMTP Connection to myExchangeSERVER" using "Windows Authentication"
However, as we all know - you can't have html format for the Send Mail Task. BUT this piece of code straight from MSDN doesnt work for me - each time it pops up this "Mail Sent Succesfully" - but I receive NO freaking EMAILs!!! Am I missing something or is it another one of those Microsoft "gotchas" ?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail

Public Class ScriptMain
Public Sub Main()
Dim htmlMessageTo As String = "me.here@mydomain.com"
Dim htmlMessageFrom As String = "SSIS@mydomain.com"
Dim htmlMessageSubject As String = "SSIS Success - My Package"
Dim htmlMessageBody As String = _
Dts.Variables("User::HTMLtemplateText").Value.ToString
Dim smtpServer As String = "myExchangeSERVER"
SendMailMessage( _
htmlMessageTo, htmlMessageFrom, _
htmlMessageSubject, htmlMessageBody, _
True, smtpServer)

Dts.TaskResult = Dts.Results.Success

End Sub

Private Sub SendMailMessage( _
ByVal SendTo As String, ByVal From As String, _
ByVal Subject As String, ByVal Body As String, _
ByVal IsBodyHtml As Boolean, ByVal Server As String)

Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient

htmlMessage = New MailMessage( SendTo, From, Subject, Body)
htmlMessage.IsBodyHtml = IsBodyHtml

mySmtpClient = New SmtpClient(Server)
Dim myCred As New System.Net.CredentialCache()
mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)
MsgBox("Mail sent")
End Sub

View 3 Replies View Related

Replace Does Not Seem To Work

Mar 18, 2008

My function keeps going into a loop. The fucntion removes html tags. It works great with everything except with this one 4,363 character string. I'm not quite sure what the problem is, please advise.

Thank you.
I may have a bunch of extra declarations that dont make sense to you but they were part of my debugging...
Also don't let all the code distract you, the long string is just the value of the html tag I'm trying to replace with nothing.
Also there are a bunch of replaces before the while loop but the magic happens in the while loop.
i've assiged to @temp the only part that my html code that replace could not replace.

Every other tag I could see the function removing, it would find the beginning of the tag, end of tag, substring it and replace it. but this one it would find it substsring it and was unable to replace it and comes back with "String or binary data would be truncated." and then it loops infinetly because it keeps finding the tags it could not remove.

please help. thank you.

also.. I'm not sure if you call can see what I see but when I review this a bunch of emoticons show up? not sure why I did not pu them there, just relate them to their correspnonding code, colon uppercase P, :-# colon dash pound, colon uppercase S.


declare @temp nvarchar(max), @input nvarchar(max), @count1 int, @count2 int, @wtf nvarchar(30), @blankString nvarchar(max), @beginBlanks int, @countBlanks int, @removeString nvarchar(max), @openTagPos int, @closeTagPos int, @emailString nvarchar(max)

set @temp = 'h<!--a:link {mso-style-priority:99;} span.MSOHYPERLINK {mso-style-priority:99;} a:visited {mso-style-priority:99;} span.MSOHYPERLINKFOLLOWED {mso-style-priority:99;} p {mso-style-priority:99;} p.MSOACETATE {mso-style-priority:99;} li.MSOACETATE {mso-style-priority:99;} div.MSOACETATE {mso-style-priority:99;} span.BALLOONTEXTCHAR {mso-style-priority:99;} span.BALLOONTEXTCHAR0 {mso-style-priority:99;} span.BALLOONTEXTCHAR00 {mso-style-priority:99;} span.BALLOONTEXTCHAR000 {mso-style-priority:99;} span.BALLOONTEXTCHAR0000 {mso-style-priority:99;} /* Font Definitions */ @font-face {font-family:Tahoma; panose-1:2 11 6 4 3 5 4 4 2 4;} @font-face {font-family:Calibri;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:12.0pt; font-family:"Times New Roman";} a:link, span.MsoHyperlink {color:blue; text-decoration:underline;} a:visited, span.MsoHyperlinkFollowed {colorurple; text-decoration:underline;} p {mso-margin-top-alt:auto; margin-right:0in; mso-margin-bottom-alt:auto; margin-left:0in; font-size:12.0pt; font-family:"Times New Roman";} p.MsoAcetate, li.MsoAcetate, div.MsoAcetate {margin:0in; margin-bottom:.0001pt; font-size:8.0pt; font-family:Tahoma;} span.BalloonTextChar {font-family:Tahoma;} span.balloontextchar0 {font-family:Tahoma;} span.balloontextchar00 {font-family:Tahoma;} span.balloontextchar000 {font-family:Tahoma;} span.balloontextchar0000 {font-family:Tahoma;} span.EmailStyle24 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle25 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle26 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle27 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle28 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle29 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle30 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle31 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle32 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle33 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle34 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle35 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle36 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle37 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle38 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle39 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle40 {mso-style-typeersonal; font-family:Arial; color:navy;} span.EmailStyle41 {mso-style-typeersonal; font-family:Calibri; color1F497D;} span.EmailStyle43 {mso-style-typeersonal-reply; font-family:Arial; color:navy;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in;} div.Section1 {pageection1;} /* List Definitions */ @list l0 {mso-list-id:223109352; mso-list-type:hybrid; mso-list-template-ids:1861408968 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:.5in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level2 {mso-level-tab-stop:1.0in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level3 {mso-level-tab-stop:1.5in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level4 {mso-level-tab-stop:2.0in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level5 {mso-level-tab-stop:2.5in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level6 {mso-level-tab-stop:3.0in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level7 {mso-level-tab-stop:3.5in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level8 {mso-level-tab-stop:4.0in; mso-level-number-position:left; text-indent:-.25in;} @list l0:level9 {mso-level-tab-stop:4.5in; mso-level-number-position:left; text-indent:-.25in;} @list l1 {mso-list-id:1020744073; mso-list-template-ids:-1621449136;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} -->i'


set @input = @temp

set @emailString = @input

set @emailString = replace(@emailString,'''','''''')

set @openTagPos = charindex('<', @emailString)

set @closeTagPos = charindex('>', @emailString)

set @emailString = replace(@emailString,'> <','><')

set @emailString = replace(@emailString,'&nbsp;','')

set @emailString = replace(@emailString,'v:* {behavior:url(#default#VML);}','')

set @emailString = replace(@emailString,'o:* {behavior:url(#default#VML);}','')

set @emailString = replace(@emailString,'w:* {behavior:url(#default#VML);}','')

set @emailString = replace(@emailString,'.shape {behavior:url(#default#VML);}','')

set @emailString = replace(@emailString,'st1:*{behavior:url(#default#ieooui) }','')

set @emailString = replace(@emailString,'&#8217;','''')

set @emailString = replace(@emailString,'&#8220;','"')

set @emailString = replace(@emailString,'&#8221;','"')

set @emailString = replace(@emailString,'&#8230;','...')

set @emailString = replace(@emailString,'&#8211;','-')

set @emailString = replace(@emailString,'t./','')


While (@openTagPos + @closeTagPos) <> 0

BEGIN

SET @removeString = substring(@emailstring, @openTagPos, @closeTagPos - @openTagPos + 1)

set @emailString = replace(@emailString,@removeString,'')
--This select is just to see what is happening

select len(@emailString) sizeOfString,@removeString stringToRemove, @opentagpos beginOfTag, @closetagpos endOfTag, @emailstring theString

set @openTagPos = charindex('<', @emailstring)

set @closeTagPos = charindex('>', @emailstring)

END

select @emailString

View 8 Replies View Related

REPLACE Function Doesn't Work With Null-bytes

Feb 7, 2006

Dear Community,We have a problem with null-Bytes in varchar-Columns, which are nothandled correctly in our application. Therefor we try to filter themout using the Transact-SQL REPLACE function.The Problem was, that the REPLACE-function didn't behave the way weexpected.Following Example demonstrates the behavior:declare @txt varchar(512)declare @i intset @txt = 'hello ' + char(0) + 'world'print @txtset @i = 1while @i <= len(@txt)beginprint str(@i) + substring(@txt, @i, 1)set @i = @i + 1endprint 'Length: ' + str(len(@txt))print 'trying to replace null-byte:'print replace(@txt, char(0), '*')print 'replace Letter h'print replace(@txt, 'h', char(39))-- end exampleOutput:hello1h2e3l4l5o678w9o10r11l12dLength: 12trying to replace null-byte:*replace Letter h'elloThe Null-Byte replace destroys the whole string. This behavior occursonly on some of ourdatabases. The others work correctly.Is it possible that it depends on some server setting?ThanksEnno

View 5 Replies View Related

How To Replace A Default Value With Parameter From A Where Clause

Jul 19, 2007

Table-Account0
column   EAIndex  ã€?BillerCodeã€?ChainCodeã€?PostDateã€?PostAmountã€?BalanceDateã€?BillBegDateã€?BillEndDateã€?BillCountã€?ChAmount
 Table-Biller
 CompanyCodeã€?BilerCodeã€?BillerNameã€?UniformNoã€?BusCodeã€?AcctNoã€?BillerInfoList
Table-DataBackup
column CompanyCode�Keepmonth
 in Table-Account0  , I use this sql query to retrieve data by column PostDate-3
Select * From ZT_EAccount0  Where isNumeric(PostDate) = 1 And PostDate < Convert(varchar(10),DateAdd(Month,-3,GetDate()),112)
now , I don't won't to lock the value "-3" , the subtract value will depend on the value of colum keepmonth from table DataBackup
can you please tell me how to do ?
 
thank you very much
 

View 4 Replies View Related

Transact SQL :: Parameter Replace And Add Quotes

Sep 25, 2015

Declare @tragetdb Varchar(max)
SET @tragetdb='xyz1'
 Declare @RestoreCmd Varchar(max)
SET @RestoreCmd= 'RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT='+''+ ' @targetdb'+ ''
 print @RestoreCmd

O/p:
RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT= @targetdb

But i am looking for 
RESTORE DATABASE XYZ FROM DATABASE_SNAPSHOT= 'xyz1'

 i tried using "" + "" , ' + ' , no luck..

View 5 Replies View Related

Parameter Value Does Not Work

May 15, 2008

Hi,

I use this code for my report.




Code Snippet
SELECT Portfolio,
RptSection,
RepExcRsn,
Report,
SUM( Units) as TotUnits ,
FROM Exc_SummaryData_Custom
WHERE (Report = 'Exc') AND (RepExcRsn = 'Grand Total') AND (RptSection ='New') AND (Portfolio IN (@Portfolio))
GROUP BY Report, RptSection,Portfolio, RepExcRsn

UNION ALL
SELECT DISTINCT 'ALL UP' AS Portfolio,
RptSection,
'Grand Total' AS RepExcRsn,Report ,
(SELECT SUM(Units) FROM Exc_SummaryData_Custom WHERE Portfolio IN
('CBank','DTC','EDirect', 'InstLend')AND (Report = 'Exc') AND
(RepExcRsn = 'Grand Total') AND (RptSection ='New') ) as TotUnits ,
FROM Exc_SummaryData_Custom
WHERE (Report = 'Exc') AND (RepExcRsn = 'Grand Total') AND (RptSection ='New') AND (Portfolio IN (@Portfolio))
GROUP BY Report, RptSection, Portfolio, RepExcRsn



Values for Portfolio parameter are like DTC, CBank, ED and ALL UP
But when I input ALL UP for portfolio parameter , I can not see anything. Originaly ALL UP is not in the table. Thats why i use union all here.
can anyone tell me why I dont get values for ALL UP?Thanks

View 6 Replies View Related

Problem Getting LIKE @parameter% To Work

Mar 25, 2004

Hello,

I need a text box that the user puts in part of a name and hits find and it returns the values that contain the words. so i want the nvarchar value to go into the standard SQL statement below.

SELECT *
FROM table
WHERE column_name LIKE 'nvarchar%'

It works fine in when i type it in manually.

But im using a stored procedure from VS and it will not work with the '%' part

SELECT *
FROM table
WHERE column_name LIKE @parameter%


Any help or ideas would be greatly appreciated.

View 5 Replies View Related

Problem Getting LIKE @parameter% To Work

Mar 25, 2004

Hello,

I need a text box that the user puts in part of a name and hits find and it returns the values that contain the words. so i want the nvarchar value to go into the standard SQL statement below.

SELECT *
FROM table
WHERE column_name LIKE 'nvarchar%'

It works fine in when i type it in manually.

But im using a stored procedure from VS and it will not work with the '%' part

SELECT *
FROM table
WHERE column_name LIKE @parameter%

Any help or ideas would be greatly appreciated.

View 4 Replies View Related

Parameter Values Doe Not Work

May 16, 2008

Hi,

I use following code to generate a report.




Code Snippet
SELECT Portfolio,
RptSection,
RepExcRsn,
Report,
SUM( Units) as TotUnits ,
FROM Exc_SummaryData_Custom
WHERE (Report = 'Exc') AND (RepExcRsn = 'Grand Total') AND (RptSection ='New') AND (Portfolio IN (@Portfolio))
GROUP BY Report, RptSection,Portfolio, RepExcRsn

UNION ALL

SELECT DISTINCT 'ALL UP' AS Portfolio,
RptSection,
'Grand Total' AS RepExcRsn,
Report ,
CASE WHEN Portfolio = 'ALL UP' THEN (SELECT SUM(Units) FROM Exc_SummaryData_Custom
WHERE Portfolio IN ('CBank','DTC','EDirect', 'InstLend','ALL UP')AND (Report = 'Exc')
AND (RepExcRsn = 'Grand Total') AND (RptSection ='New') )
ELSE 0
END as TotUnits ,
FROM Exc_SummaryData_Custom
WHERE (Report = 'Exc') AND (RepExcRsn = 'Grand Total') AND (RptSection ='New')
GROUP BY Report, RptSection, Portfolio, RepExcRsn





My problem is when I input a value for Portfolio parameter, i get two rows instead of one. If I input 'ALL UP'
then I get value only for ALL UP. But if I input smthing else , then I get values for that paramter value as well as ALL UP thus giving me two rows not just one as I want.
Case stament always gives me 0 value for ALL UP which is not the true value.
can anyone help me to correct this?

View 3 Replies View Related

SQL Parameter And Wildcards, How To Make It Work?

Oct 22, 2007



Hello, I have what should be a very simple problem, but I cant solve it.
I want to have a stored procedure return a table query (no problems here) but I also need to supply several parameters to the stored procedure (again, no problem!)

Here is the problem, I need to be able to supply a wildcard into the stored procedure as an argument somehow. I can do this already, but the results are incorrect!!! It seems like when local variables are used, the wildcard argument gets ignored. for example, I have included the following example:


DECLARE @Dv_id nchar(15)

SET @Drv_id = '%'



SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE @Dv_id



SELECT Diver.*, (ROW_NUMBER() OVER(ORDER BY Dv_id)) as RowNum FROM Diver WHERE Dv_id LIKE '%'

OK, this is an example of my problem, the results I get from this are that the fist SELECT return 0 rows.
The second SELECT returns the correct number of rows (everything in the table). Why is there a difference between:
WHERE Drv_id LIKE @Drv_id
and
WHERE Drv_id LIKE '%'
?
The wildcard statement '%' is supposed match everything, correct??
It seems like the local variable SET command syntax eats up my value of '%' and turns it into a NULL.

Is there any way around this?

View 2 Replies View Related







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