db datatypes: code

Showing posts with label code. Show all posts

Wednesday, March 28, 2012

Problem with JOIN

I'm learning aspx. I have the following code in my aspx:
SqlConnection connection = new
SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
Security=SSPI");
String sqlCommand = "SELECT * FROM Titles INNER JOIN MyTable ON
MyTable.title_id=Titles.title_id";
SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
..
..
..
This code returns the correct data set. However, when I use this code:
SqlConnection connection = new
SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
Security=SSPI");
String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
notes, pubdate FROM Titles INNER JOIN MyTable ON
MyTable.title_id=Titles.title_id";
SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
..
..
..
the returned data set does not contain the MyTable data.
What is Wrong?
"RuffAroundTheEdges" schrieb:
> I'm learning aspx. I have the following code in my aspx:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT * FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> This code returns the correct data set. However, when I use this code:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
> notes, pubdate FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> the returned data set does not contain the MyTable data.
> What is Wrong?
You are not asking for data from MyTable. All the fields in the select list
are fields from 'titles', aren't they?
|||If you place the star (*) in the selection list without any prefix for any
table, even all joined tables are used for selecting the columns, If you are
specifying the columns only these are selected.
So in your second example you should go for that statement if you want to
get those columns in addition:

> String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
> notes, pubdate,
myTable.*
FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"RuffAroundTheEdges" <RuffAroundTheEdges@.discussions.microsoft.com> wrote in
message news:AC882F99-C17E-43BF-843E-0EF0D0B4ADC1@.microsoft.com...
> I'm learning aspx. I have the following code in my aspx:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT * FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> .
> .
> This code returns the correct data set. However, when I use this code:
> SqlConnection connection = new
> SqlConnection("server=(local)\\NetSDK;database=pub s;Integrated
> Security=SSPI");
> String sqlCommand = "SELECT Titles.title_id, title, type, pub_id, price,
> notes, pubdate FROM Titles INNER JOIN MyTable ON
> MyTable.title_id=Titles.title_id";
> SqlDataAdapter command = new SqlDataAdapter(sqlCommand, connection);
> .
> .
> .
> the returned data set does not contain the MyTable data.
> What is Wrong?
|||The example shown in the FROM document page at;
http://msdn.microsoft.com/library/de..._pubs_2v8l.asp
has:
SELECT ProductID, Suppliers.SupplierID
FROM Suppliers JOIN Products
ON (Suppliers.SupplierID = Products.SupplierID)
|||I had to do more reading and found that the SELECT statement works
differently in a JOIN:
SELECT Titles.title.id, ..., MyTable.stuff FROM Titles JOIN MyTable ON
(MyTable.title_id = Titles.title_id)

Monday, March 26, 2012

Problem with insert Time to database

Dear all,
I have insert the time to my database but it appear also the date by default.
This is my code in C# :
DateTime date = DateTime.Now;
int hour = date.Hour;
int minute = date.Minute;
int second = date.Second;
string requestedTime = hour+":"+minute+":"+second;
string query = "INSERT INTO workorder([timeRequest]) VALUES("'"+requestTime+"'");
in my database , the column timeRequest appear :1/1/1900 11:59:05 AM
I dont want the date by default to appear, i want only the time like : 11:59:05 AM in my database,
Anyone can help me?
Best Regards,
Moniphal

Use parameterized command to insert data to database. By example:

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO workorder(timeRequest) VALUES (@.TimeRequest)";
cmd.CommandType = CommandType.Text
cmd.Parameters.Add(new SqlParameter("@.TimeRequest",myDateTime));
cmd.ExecuteNonQuery();

Wednesday, March 21, 2012

Problem with HTML slightly garbled sending email as web archive

I'm experiencing a problem with HTML code being slightly messed up when
sending a subscription via email in web archive format.
The report displays fine in the Report Manager. However, went emailed some
whitespace is seemingly randomly added to the HTML page. Depending on where
that whitespace is, this might not be a problem. But if the whitespace is
inserted in some places within an HTML tag, then the tag isn't rendered
properly.
Example: a space is added following the "<" in "</DIV>", thus giving "<
/DIV>". Since this is invalid HTML, it displays as text in the report.
Example: a cell in a table is defined as right-aligned and in a specific
font. All 25 rows in the table (x 3 columns = 75 cells) all display
correctly:
<P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
face="Eras Medium ITC" color=black size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
Medium ITC'">
*EXCEPT* one cell, where the following HTML is used:
<P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt">
Why did RS render this cell differently?This might be an issue with the local SMTP. Are you using the pickup
directory to send email?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
> I'm experiencing a problem with HTML code being slightly messed up when
> sending a subscription via email in web archive format.
> The report displays fine in the Report Manager. However, went emailed
> some
> whitespace is seemingly randomly added to the HTML page. Depending on
> where
> that whitespace is, this might not be a problem. But if the whitespace is
> inserted in some places within an HTML tag, then the tag isn't rendered
> properly.
> Example: a space is added following the "<" in "</DIV>", thus giving "<
> /DIV>". Since this is invalid HTML, it displays as text in the report.
> Example: a cell in a table is defined as right-aligned and in a specific
> font. All 25 rows in the table (x 3 columns = 75 cells) all display
> correctly:
> <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
> face="Eras Medium ITC" color=black size=2><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
> Medium ITC'">
> *EXCEPT* one cell, where the following HTML is used:
> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
> style="FONT-SIZE: 12pt">
> Why did RS render this cell differently?|||Thanks for your reply, Daniel.
I've tried this with two SMTP mail servers, both with the same result. I
changed the definition via the RSReportServer.config file.
Neither SMTP server is on my local machine.
One resides on a Win2003 box and is a MS mail server; the other resides on a
Win2000 Server box and is a product called Merak Mail Server. Ultimately
they send via the Columbia University mail server, which is sendmail, I
believe.
(I'm not a systems guy so sorry if my answers aren't dead-on what you asked
for.)
Please also see my comments on another post title "Web archive subrsciption
gets messed up" (note misspelling of 'subscription'). I ran into an
identical problem like this when sending email from an Oracle PL/SQL program.
The resolution is to intersprese CRLF into the msg at least every 990 bytes.
Thanks Daniel. This is the one problem that's keeping me from implementing
RS and beginning to retire Crystal Enterprise.
"Daniel Reib [MSFT]" wrote:
> This might be an issue with the local SMTP. Are you using the pickup
> directory to send email?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
> news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
> > I'm experiencing a problem with HTML code being slightly messed up when
> > sending a subscription via email in web archive format.
> >
> > The report displays fine in the Report Manager. However, went emailed
> > some
> > whitespace is seemingly randomly added to the HTML page. Depending on
> > where
> > that whitespace is, this might not be a problem. But if the whitespace is
> > inserted in some places within an HTML tag, then the tag isn't rendered
> > properly.
> >
> > Example: a space is added following the "<" in "</DIV>", thus giving "<
> > /DIV>". Since this is invalid HTML, it displays as text in the report.
> >
> > Example: a cell in a table is defined as right-aligned and in a specific
> > font. All 25 rows in the table (x 3 columns = 75 cells) all display
> > correctly:
> > <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
> > face="Eras Medium ITC" color=black size=2><SPAN
> > style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: 'Eras
> > Medium ITC'">
> > *EXCEPT* one cell, where the following HTML is used:
> > <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
> > style="FONT-SIZE: 12pt">
> > Why did RS render this cell differently?
>
>|||I haven't done this yet but what was suggested to me (I too am having
problems and am using an external smtp server too) was to use Windows 2003
smtp service and have it forward/route (not sure the term) to the external
smtp server. The Windows 2003 smtp server is configured to be looking at a
directory and forwarding things on from there. Then you have your report
saved to the pickup directory. Note that I haven't done this so I am a
little vague on how it all works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Columbia Kai" <ColumbiaKai@.discussions.microsoft.com> wrote in message
news:8995F878-CB68-4766-BB00-654D7580BAF1@.microsoft.com...
> Thanks for your reply, Daniel.
> I've tried this with two SMTP mail servers, both with the same result. I
> changed the definition via the RSReportServer.config file.
> Neither SMTP server is on my local machine.
> One resides on a Win2003 box and is a MS mail server; the other resides on
> a
> Win2000 Server box and is a product called Merak Mail Server. Ultimately
> they send via the Columbia University mail server, which is sendmail, I
> believe.
> (I'm not a systems guy so sorry if my answers aren't dead-on what you
> asked
> for.)
> Please also see my comments on another post title "Web archive
> subrsciption
> gets messed up" (note misspelling of 'subscription'). I ran into an
> identical problem like this when sending email from an Oracle PL/SQL
> program.
> The resolution is to intersprese CRLF into the msg at least every 990
> bytes.
> Thanks Daniel. This is the one problem that's keeping me from
> implementing
> RS and beginning to retire Crystal Enterprise.
>
> "Daniel Reib [MSFT]" wrote:
>> This might be an issue with the local SMTP. Are you using the pickup
>> directory to send email?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Columbia Kai" <Columbia Kai@.discussions.microsoft.com> wrote in message
>> news:A0190874-9273-4F1C-9BD1-CF0CEB79616D@.microsoft.com...
>> > I'm experiencing a problem with HTML code being slightly messed up when
>> > sending a subscription via email in web archive format.
>> >
>> > The report displays fine in the Report Manager. However, went emailed
>> > some
>> > whitespace is seemingly randomly added to the HTML page. Depending on
>> > where
>> > that whitespace is, this might not be a problem. But if the whitespace
>> > is
>> > inserted in some places within an HTML tag, then the tag isn't rendered
>> > properly.
>> >
>> > Example: a space is added following the "<" in "</DIV>", thus giving "<
>> > /DIV>". Since this is invalid HTML, it displays as text in the report.
>> >
>> > Example: a cell in a table is defined as right-aligned and in a
>> > specific
>> > font. All 25 rows in the table (x 3 columns = 75 cells) all display
>> > correctly:
>> > <P class=MsoNormal style="TEXT-ALIGN: right" align=right><FONT
>> > face="Eras Medium ITC" color=black size=2><SPAN
>> > style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY:
>> > 'Eras
>> > Medium ITC'">
>> > *EXCEPT* one cell, where the following HTML is used:
>> > <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN
>> > style="FONT-SIZE: 12pt">
>> > Why did RS render this cell differently?
>>sql

Problem with Having and Max

Hello,
I have the following query:
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
Group By
Code,
TestNo
Having
TestNo = Max(TestNo)
Order By
Code
What I want to achieve is summing only the weight for Max(TestNo) for each
code. But since I have to group by TestNo to use it in the Having clause it
is giving me the sum for all TestNo for each Code.
How can I achieve summing only the weight for Max(TestNo) for each code?
Thanks.
MichelTry,
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
#tWidra.Code)
Group By
Code,
TestNo
Order By
Code
AMB
"Michel Hardy" wrote:

> Hello,
> I have the following query:
> Select
> #tWidra.Code,
> Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
> Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
> From
> #tWidra
> Where
> TestCode = 20000
> Group By
> Code,
> TestNo
> Having
> TestNo = Max(TestNo)
> Order By
> Code
> What I want to achieve is summing only the weight for Max(TestNo) for each
> code. But since I have to group by TestNo to use it in the Having clause i
t
> is giving me the sum for all TestNo for each Code.
> How can I achieve summing only the weight for Max(TestNo) for each code?
> Thanks.
> Michel|||Correction,
Select
#tWidra.Code,
Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
From
#tWidra
Where
TestCode = 20000
and TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
#tWidra.Code)
Group By
Code,
TestNo
Order By
Code
AMB
"Alejandro Mesa" wrote:
> Try,
> Select
> #tWidra.Code,
> Sum(Convert(Decimal(14, 4), #tWidra.GrossWeight)) As GrossWeight,
> Sum(Convert(Decimal(14, 4), #tWidra.NetWeight)) As NetWeight
> From
> #tWidra
> Where
> TestCode = 20000
> TestNo = (select max(a.TestNo) from #tWidra as a where a.Code =
> #tWidra.Code)
> Group By
> Code,
> TestNo
> Order By
> Code
>
> AMB
> "Michel Hardy" wrote:
>

Problem with GROUP BY syntax and expression

I am really struggling with this code and would appreciate knowing how
to group by the expression (constants) in the SELECT clause:
DECLARE @.LO INT
DECLARE @.HI INT
DECLARE @.StartDate varchar(10)
DECLARE @.EndDate varchar(10)
SELECT @.StartDate = '01/01/2005'
SELECT @.EndDate = '06/30/2005'
SELECT @.LO = 250
SELECT @.HI = 333
SELECT
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDate
, JumboAmount = SUM(JumboAmount)
, JumboMortgages = SUM(JumboMortgages)
, JumboFIXMortgages = SUM(JumboFIXMortgages)
, JumboFIXAmount = SUM(JumboFIXAmount)
, JumboARMMortgages = SUM(JumboARMMortgages)
, JumboARMAmount = SUM(JumboARMAmount)
FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
Z.ZipCode
GROUP BY
StateCD
, CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
, 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
Convert(varchar(4),@.HI)
, 'StartingDate' = @.StartDate
, 'ThruDate' = @.EndDateRemove the aliases from the GROUP BY.
GROUP BY
StateCD
,CountyCD
, Zip
, Z.CityName
, Z.StateCode
, Z.CountyName
,'JumboRange:' + Convert(varchar(4),@.LO) + '-' + Convert(varchar(4),@.HI)
,@.StartDate
,@.EndDate
"JJA" <johna@.cbmiweb.com> wrote in message
news:1123780031.845579.256660@.o13g2000cwo.googlegroups.com...
> I am really struggling with this code and would appreciate knowing how
> to group by the expression (constants) in the SELECT clause:
> DECLARE @.LO INT
> DECLARE @.HI INT
> DECLARE @.StartDate varchar(10)
> DECLARE @.EndDate varchar(10)
> SELECT @.StartDate = '01/01/2005'
> SELECT @.EndDate = '06/30/2005'
> SELECT @.LO = 250
> SELECT @.HI = 333
> SELECT
> StateCD
> , CountyCD
> , Zip
> , Z.CityName
> , Z.StateCode
> , Z.CountyName
> , 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
> Convert(varchar(4),@.HI)
> , 'StartingDate' = @.StartDate
> , 'ThruDate' = @.EndDate
> , JumboAmount = SUM(JumboAmount)
> , JumboMortgages = SUM(JumboMortgages)
> , JumboFIXMortgages = SUM(JumboFIXMortgages)
> , JumboFIXAmount = SUM(JumboFIXAmount)
> , JumboARMMortgages = SUM(JumboARMMortgages)
> , JumboARMAmount = SUM(JumboARMAmount)
> FROM LoanDetails T INNER JOIN dbo.ZipCodesPreferred Z
> ON T.StateCD = Z.FIPS_State AND T.CountyCD = Z.FIPS_County AND T.Zip =
> Z.ZipCode
> GROUP BY
> StateCD
> , CountyCD
> , Zip
> , Z.CityName
> , Z.StateCode
> , Z.CountyName
> , 'Criteria' = 'JumboRange:' + Convert(varchar(4),@.LO) + '-' +
> Convert(varchar(4),@.HI)
> , 'StartingDate' = @.StartDate
> , 'ThruDate' = @.EndDate
>|||I would use a table expression, like this:
create table t(i int, d money)
insert into t values(1, 1.00)
insert into t values(1, 2.00)
insert into t values(2, 3.00)
insert into t values(2, 4.00)
select t.*, 'Criteria' = 'Some text here'
from (select i, sum(d) sumd from t group by i) t
drop table t
Besides, what's the point of grouping by both CountyCD and CountyName?
If you group by only by StateCD and CountyCD, the query might run much
faster. The rest columns could be retrieved after grouping by, like
this:
create table t(i int, d money)
insert into t values(1, 1.00)
insert into t values(1, 2.00)
insert into t values(2, 3.00)
insert into t values(2, 4.00)
create table s(i int, sname char(5))
insert into s values(1,'One')
insert into s values(2,'Two')
select s.sname, t.sumd, 'Criteria' = 'Some text here'
from (select i, sum(d) sumd from t group by i) t
join s on s.i=t.i
sname sumd Criteria
-- -- --
One 3.0000 Some text here
Two 7.0000 Some text here
drop table t
drop table s|||Thank you so much. Great idea...I now have it working per your
suggestion.

Problem with group by

Hi all

I have a table with customerid, productcode & seqno.

customerid code seqno
08117701 222 1
08117701 223 2
08117701 224 3
20106401 441 1
20106401 442 2

what I need to return is:

customerid code seqno
08117701 224 3
20106401 442 2

the details from the top seqno from each record.

Thanks inadvance

Rich

Try the example below.

Chris

Code Snippet

DECLARE @.MyTable TABLE(CustomerID VARCHAR(8), Code INT, SeqNo INT)

INSERT INTO @.MyTable(CustomerID, Code, SeqNo)

SELECT '08117701', 222, 1 UNION

SELECT '08117701', 223, 2 UNION

SELECT '08117701', 224, 3 UNION

SELECT '20106401', 441, 1 UNION

SELECT '20106401', 442, 2

SELECT t.CustomerID,

t.Code,

t.SeqNo

FROM

(SELECT CustomerID,

Code,

SeqNo,

ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY SeqNo DESC) AS [RowNumber]

FROM @.MyTable) t

WHERE t.[RowNumber] = 1

|||

Hi Chris

Thanks for the reply but I am using sql 2000 and row_number is not a recongized function name

Regards

Rich

|||

here you go (with 2000)

Code Snippet

Create Table #data (

[customerid] int ,

[code] int ,

[seqno] int

);

Insert Into #data Values('08117701','222','1');

Insert Into #data Values('08117701','223','2');

Insert Into #data Values('08117701','224','3');

Insert Into #data Values('20106401','441','1');

Insert Into #data Values('20106401','442','2');

Select Main.* From #Data Main

Join (Select customerid,max(seqno) seqno From #data Group By customerid) as TopSeq

On Main.SeqNo = TopSeq.SeqNo And Main.customerid = TopSeq.customerid;

Drop table #data;

|||I found this thread

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=144651&SiteID=1

see if it helps

Cheers
|||

declare @.MyTable table

(CustomerID VARCHAR(8), Code INT, SeqNo INT)

INSERT INTO @.MyTable(CustomerID, Code, SeqNo)

SELECT '08117701', 222, 1 UNION

SELECT '08117701', 223, 2 UNION

SELECT '08117701', 224, 3 UNION

SELECT '20106401', 441, 1 UNION

SELECT '20106401', 442, 2

select * from @.MyTable m where SeqNo in (select max(SeqNo) from @.MyTable where CustomerId = m.CustomerId)

Is this what ur looking for ?

Tuesday, March 20, 2012

problem with functions and datetime parameters!

Hi all.
i've written a portion of sql code with a dtetime parameter that run very
very fast on a sql window, but when i create a function with the same code
the execution time is extremely long!
to recreate the same speed i found that i must declare a local variable
inside the scope of the function and then assign the variable passed to the
function.
does anyone had the same problem? there is a solution to this bug?
this is my code...
regards,
stefano
create function kp.getQuotaHWM (@.dd1 datetime)
returns float
as
begin
declare @.dd datetime
set @.dd = @.dd1
return (
... code of the function
)
endOn Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:

>Hi all.
>i've written a portion of sql code with a dtetime parameter that run very
>very fast on a sql window, but when i create a function with the same code
>the execution time is extremely long!
>to recreate the same speed i found that i must declare a local variable
>inside the scope of the function and then assign the variable passed to the
>function.
>does anyone had the same problem? there is a solution to this bug?
Hi stefano,
This is a known issue. Not exactly a bug - more an unwanted side effect
of a wanted feature.
Search this group (or the internet) for "parameter sniffing" to find
alll the details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo.
many thanks for your informations.
regards, stefano
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:q5iff1pndv10nhhr32c650b9vb5evi0fkb@.
4ax.com...
> On Mon, 8 Aug 2005 11:09:41 +0200, stefano wrote:
>
> Hi stefano,
> This is a known issue. Not exactly a bug - more an unwanted side effect
> of a wanted feature.
> Search this group (or the internet) for "parameter sniffing" to find
> alll the details.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, March 12, 2012

problem with executing an sqlcommand

when i push a button my datagrid doesn't show up. please help this is my code. I only try to execute one of the sql command and when i push the button it just redirects me to my homepage.

Private Sub btnbid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbid.Click

Dim conn As New SqlConnection((Application("SQL_Connection_String")))

conn.Open()

Dim cmdbid As New SqlCommand("insert into Bids (CustID,ItemCode,BidAmount) values (@.CustID,@.ItemCode,@.BidAmount)", conn)

cmdbid.Parameters.Add("@.CustID", SqlDbType.Int, 4)
cmdbid.Parameters("@.CustID").Value = Session("CustID")
cmdbid.Parameters.Add("@.ItemCode", SqlDbType.Int, 4)
cmdbid.Parameters("@.ItemCode").Value = CInt(Request.QueryString("Id"))
cmdbid.Parameters.Add("@.BidAmount", SqlDbType.Decimal, 9)
cmdbid.Parameters("@.BidAmount").Value = CDec(txtbidamount.Text)

Dim cmdhighbid As New SqlCommand("update items set Highestbid=@.BidAmount,HighestBidder=@.bidder where ItemCode=@.ItemCode and Highestbid<@.BidAmount", conn)

cmdhighbid.Parameters.Add("@.BidAmount", SqlDbType.Decimal, 9)
cmdhighbid.Parameters("@.BidAmount").Value = CDec(txtbidamount.Text)
cmdhighbid.Parameters.Add("@.bidder", SqlDbType.Int, 4)
cmdhighbid.Parameters("@.bidder").Value = Session("CustID")
cmdhighbid.Parameters.Add("@.ItemCode", SqlDbType.Int, 4)
cmdhighbid.Parameters("@.ItemCode").Value = Request.QueryString("Id")

Try
cmdbid.ExecuteNonQuery()

lblbidstatus.Text = "Bid Inserted Successfully!! Good Luck!!!"
Catch ex As Exception

lblbidstatus.Text = ex.Message
End Try

btnbid.Enabled = False
conn.Close()
End Subare you binding the datagrid within if not ispostback loop in page_load.. ?

hth|||i don't have anything in the page load only the bindgrid() proc.|||because you are prbly losing the datagrid during postbck. try this


sub page_load(...)
if not ispostback() then
 bindgrid()
end if
end sub

hth|||no that's not the problem. when i push the button it redirects me to the homepage with no reason and does not do any changes i do with the sql command|||i dont see anything in your code to redirect to another page. are you sure you are calling the right event ?

Problem with EXEC xp_cmdshell in UDF's

Hi All

Some what of a noob to UDF's and the EXEC statement...
I am confused on the following code....

CREATE FUNCTION DBO.DoesFileExist(@.FileLocation NVARCHAR(500))
RETURNS INT AS
BEGIN
DECLARE @.return AS INT
DECLARE @.strCmd AS NVARCHAR(1050)
SET @.strCmd = 'IF EXIST "' + @.FileLocation + '" ECHO 0
ELSE ECHO 1'
EXEC @.return = XP_CMDSHELL @.strCmd
RETURN @.return
END

GO

DECLARE @.FileLocation AS NVARCHAR(500)
SET @.FileLocation = 'C:\to\log.txt'

DECLARE @.return AS INT
DECLARE @.strCmd AS NVARCHAR(1050)
SET @.strCmd = 'IF EXIST "' + @.FileLocation + '" ECHO 0
ELSE ECHO 1'
EXEC @.return = XP_CMDSHELL @.strCmd

SELECT @.return AS Exp1

SELECT DBO.DoesFileExist(@.FileLocation) AS Exp2

RETURNS:
Exp1 = 0
Exp2 = 1

WHY IS THAT? Shouldn't Exp2 also be equal to 0? It's running the same function...

Any help would be much appreciated
Thanks
Mike
IF EXIST c:\MyFile.txt (ECHO 0) ELSE (ECHO 1)

Friday, March 9, 2012

Problem with embedded code In REporting Services 2005

Hi,
I have a problem that seems impossible to resolve in RS but i need to be sure.
I'm creating an Embedded Code in VB of course, and i need to run a query within my code to be able to continue the rest of the code depending on the result of my query.
The problem is there is no adodb.recordset or adodb.connection like in normal VB.

So i created my query like as string:

Dim Sql as String
Sql="Select ..." into "from ........where...."

At this point if it was in normal VB,
i would do
MyConnection.Execute(SQL)
and then i will get the answer in my recordset.

I need to know if there is a way in RS to run my query since I cannot use use adodb ....
If it's not really possible, is there way to go around this problem?

Thanks for the Help

Mike
I would think you would need to put your data access code in an assembly, then call the assembly in your report code, but I am not an expert. I'm not sure there is a way around doing it that way, depending on your problem. Of course, it is a bit of a pain to get the report server to use custom assemblies that use System.Data because of security. You need to dig around MSDN and look at Security for Custom Assemblies with SSRS.

Problem with dynamic SQL syntax

I' m having a problem with the syntax when I'm trying to run a dynamic SQL
statement.
The code -
set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' Where
Date_ >='+'2001-01-01'+'
And CompanyNo Is Not NULL And Type <>'
exec sp_executesql @.sql
- gives me the error "Incorrect syntax near '>'." The purpose of the last <>
is to find the records where this field is empty (that's my understanding of
it...). The basic structure of the query is from a DTS Transform task, but
I'm trying to "convert" this whole task to TSql.
I've tried all sorts of different combinations of <> and ' but it still
won't do it. If I just prins the @.sql var. it looks fine.
The CompanyNo field is int(4) and the Type field is varchar(30).
Is there any other ways to check for an empty varchar field of can some of
you guide me to what it is I'm missing in my "set @.sql...." statement?
Best Regards
SteenWhat exactly do you mean by an empty field? Generally if
your field allows null and you don't set a value into
that field for a row, the field is set to NULL and you
can test for this by using "type IS NULL" in your where
clause.
"<>" is the operator for not equals and it is not a
singleton operator, it is a comparison operator. You
need something on both sides of the "<>" to compare to
each other.
For instance, if you are looking for rows where the type
field is not equal to a space, you could try "type <> ' '"
I hope that this helps.
Matthew Bando
bandoM@.CSCTechnologies-dot-com
>--Original Message--
>I' m having a problem with the syntax when I'm trying to
run a dynamic SQL
>statement.
>The code -
>set @.sql = 'SELECT *
INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
>
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
le_name+' Where
>Date_ >='+'2001-01-01'+'
> And CompanyNo Is Not NULL And Type <>'
>exec sp_executesql @.sql
>- gives me the error "Incorrect syntax near '>'." The
purpose of the last <>
>is to find the records where this field is empty (that's
my understanding of
>it...). The basic structure of the query is from a DTS
Transform task, but
>I'm trying to "convert" this whole task to TSql.
>I've tried all sorts of different combinations of <>
and ' but it still
>won't do it. If I just prins the @.sql var. it looks fine.
>The CompanyNo field is int(4) and the Type field is
varchar(30).
>Is there any other ways to check for an empty varchar
field of can some of
>you guide me to what it is I'm missing in my "set
@.sql...." statement?
>Best Regards
>Steen
>
>.
>|||Hi
Sorry if I wasn't very clear. I assume that the purpose is to check for an
empty field. The "original" code that's being used in the DTS Transform task
is "...AND CompanyNo is not NULL and Type <>'' ". It's not me that have
written the transform task, but I assume that this last piece checks if
there're any empty fields. It might be my understanding of it that's wrong,
but then I'd be happy to hear about it.
This SQL statement runs fine in the DST task and also when I run it in Query
analyser using fixed values, but when I do it with variables/dynamic SQL it
seems to fail and not accept this last bit.
Regards
Steen
"Matthew Bando" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:071c01c46e4c$74405700$a501280a@.phx.gbl...
> What exactly do you mean by an empty field? Generally if
> your field allows null and you don't set a value into
> that field for a row, the field is set to NULL and you
> can test for this by using "type IS NULL" in your where
> clause.
> "<>" is the operator for not equals and it is not a
> singleton operator, it is a comparison operator. You
> need something on both sides of the "<>" to compare to
> each other.
> For instance, if you are looking for rows where the type
> field is not equal to a space, you could try "type <> ' '"
> I hope that this helps.
> Matthew Bando
> bandoM@.CSCTechnologies-dot-com
> >--Original Message--
> >I' m having a problem with the syntax when I'm trying to
> run a dynamic SQL
> >statement.
> >
> >The code -
> >
> >set @.sql = 'SELECT *
> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> >
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
> le_name+' Where
> >Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> >exec sp_executesql @.sql
> >
> >- gives me the error "Incorrect syntax near '>'." The
> purpose of the last <>
> >is to find the records where this field is empty (that's
> my understanding of
> >it...). The basic structure of the query is from a DTS
> Transform task, but
> >I'm trying to "convert" this whole task to TSql.
> >
> >I've tried all sorts of different combinations of <>
> and ' but it still
> >won't do it. If I just prins the @.sql var. it looks fine.
> >The CompanyNo field is int(4) and the Type field is
> varchar(30).
> >Is there any other ways to check for an empty varchar
> field of can some of
> >you guide me to what it is I'm missing in my "set
> @.sql...." statement?
> >
> >Best Regards
> >Steen
> >
> >
> >.
> >|||I think you're having a comprehension problem between an empty/blank string
(which is a valid value) and a NULL (which is an unknown/missing value).
Anyway, your syntax is wrong. It ends at ' which is the closing of the
string, so of course the EXEC call will break.
DECLARE @.sql NVARCHAR(2000)
SET @.sql = N'SELECT <column_list> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
'') != ''
You might try getting it working as a normal statement first, then putting
it into dynamic SQL. Remember that anytime you have a literal ' you must
escape it so it isn't interpreted as a string terminator.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> I' m having a problem with the syntax when I'm trying to run a dynamic SQL
> statement.
> The code -
> set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
Where
> Date_ >='+'2001-01-01'+'
> And CompanyNo Is Not NULL And Type <>'
> exec sp_executesql @.sql
> - gives me the error "Incorrect syntax near '>'." The purpose of the last
<>
> is to find the records where this field is empty (that's my understanding
of
> it...). The basic structure of the query is from a DTS Transform task, but
> I'm trying to "convert" this whole task to TSql.
> I've tried all sorts of different combinations of <> and ' but it still
> won't do it. If I just prins the @.sql var. it looks fine.
> The CompanyNo field is int(4) and the Type field is varchar(30).
> Is there any other ways to check for an empty varchar field of can some of
> you guide me to what it is I'm missing in my "set @.sql...." statement?
> Best Regards
> Steen
>|||Hi Araron
I think the ' was a remicense for my playing around with the statement, so
that's not the cause of the problem- sorry for the confusion.
I do know the difference between an empty field and NULL (or at least I hope
I know...:-)...) so I'm sorry if I've made some confusions about this. The
statement actually works when I'm running it without the variables and
without the dynamic SQL. It's not until I add the variable and dyn. SQL
part, I have problems getting it to accept the ' ' in the end. I'm not a all
experienced in using dynamic SQL so I thought that it was just something
simple I was missing in the syntax.
I'll take a closer look at your suggestion to see if that will do the trick.
Thanks
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I think you're having a comprehension problem between an empty/blank
string
> (which is a valid value) and a NULL (which is an unknown/missing value).
> Anyway, your syntax is wrong. It ends at ' which is the closing of the
> string, so of course the EXEC call will break.
> DECLARE @.sql NVARCHAR(2000)
> SET @.sql = N'SELECT <column_list> INTO
'+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
> Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
> '') != ''
> You might try getting it working as a normal statement first, then putting
> it into dynamic SQL. Remember that anytime you have a literal ' you must
> escape it so it isn't interpreted as a string terminator.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > I' m having a problem with the syntax when I'm trying to run a dynamic
SQL
> > statement.
> >
> > The code -
> >
> > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> Where
> > Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> > exec sp_executesql @.sql
> >
> > - gives me the error "Incorrect syntax near '>'." The purpose of the
last
> <>
> > is to find the records where this field is empty (that's my
understanding
> of
> > it...). The basic structure of the query is from a DTS Transform task,
but
> > I'm trying to "convert" this whole task to TSql.
> >
> > I've tried all sorts of different combinations of <> and ' but it still
> > won't do it. If I just prins the @.sql var. it looks fine.
> > The CompanyNo field is int(4) and the Type field is varchar(30).
> > Is there any other ways to check for an empty varchar field of can some
of
> > you guide me to what it is I'm missing in my "set @.sql...." statement?
> >
> > Best Regards
> > Steen
> >
> >
>|||Hi Aaron
While reading your post once more, I stumbled over your comment "..that
anytime you have a literal ' you must escape it...". What do you mean about
escape it? Do you just mean that I need a "start" and a "stop" '?
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I think you're having a comprehension problem between an empty/blank
string
> (which is a valid value) and a NULL (which is an unknown/missing value).
> Anyway, your syntax is wrong. It ends at ' which is the closing of the
> string, so of course the EXEC call will break.
> DECLARE @.sql NVARCHAR(2000)
> SET @.sql = N'SELECT <column_list> INTO
'+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
> Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
> '') != ''
> You might try getting it working as a normal statement first, then putting
> it into dynamic SQL. Remember that anytime you have a literal ' you must
> escape it so it isn't interpreted as a string terminator.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > I' m having a problem with the syntax when I'm trying to run a dynamic
SQL
> > statement.
> >
> > The code -
> >
> > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> Where
> > Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> > exec sp_executesql @.sql
> >
> > - gives me the error "Incorrect syntax near '>'." The purpose of the
last
> <>
> > is to find the records where this field is empty (that's my
understanding
> of
> > it...). The basic structure of the query is from a DTS Transform task,
but
> > I'm trying to "convert" this whole task to TSql.
> >
> > I've tried all sorts of different combinations of <> and ' but it still
> > won't do it. If I just prins the @.sql var. it looks fine.
> > The CompanyNo field is int(4) and the Type field is varchar(30).
> > Is there any other ways to check for an empty varchar field of can some
of
> > you guide me to what it is I'm missing in my "set @.sql...." statement?
> >
> > Best Regards
> > Steen
> >
> >
>|||Anytime a string contains ' you need to 'escape' it by doubling it. This
tells the engine that your ' is part of the string, and should not be
interpreted as an end-of-string marker.
Imagine your string looks like this:
Bob's Bait Shack
When you put it into a string,
SET @.string = 'Bob's Bait Shack'
Well, where does the string end? Between the b and the s, so the rest of
the string is lost. Except you will get an unclosed character string error
because other stuff follows the termination of the string.
Try these in Query Analyzer to see what I mean:
SELECT 'Bob's bait shack'
GO
SELECT 'Bob''s bait shack'
GO
SELECT 'Bob''s bait shack = ''''?'
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ucbpAImbEHA.252@.TK2MSFTNGP10.phx.gbl...
> Hi Aaron
> While reading your post once more, I stumbled over your comment "..that
> anytime you have a literal ' you must escape it...". What do you mean
about
> escape it? Do you just mean that I need a "start" and a "stop" '?
> Steen
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > I think you're having a comprehension problem between an empty/blank
> string
> > (which is a valid value) and a NULL (which is an unknown/missing value).
> >
> > Anyway, your syntax is wrong. It ends at ' which is the closing of the
> > string, so of course the EXEC call will break.
> >
> > DECLARE @.sql NVARCHAR(2000)
> > SET @.sql = N'SELECT <column_list> INTO
> '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
WHERE
> > Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND
COALESCE(RTRIM(Type),
> > '') != ''
> >
> > You might try getting it working as a normal statement first, then
putting
> > it into dynamic SQL. Remember that anytime you have a literal ' you
must
> > escape it so it isn't interpreted as a string terminator.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > > I' m having a problem with the syntax when I'm trying to run a dynamic
> SQL
> > > statement.
> > >
> > > The code -
> > >
> > > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> > Where
> > > Date_ >='+'2001-01-01'+'
> > > And CompanyNo Is Not NULL And Type <>'
> > > exec sp_executesql @.sql
> > >
> > > - gives me the error "Incorrect syntax near '>'." The purpose of the
> last
> > <>
> > > is to find the records where this field is empty (that's my
> understanding
> > of
> > > it...). The basic structure of the query is from a DTS Transform task,
> but
> > > I'm trying to "convert" this whole task to TSql.
> > >
> > > I've tried all sorts of different combinations of <> and ' but it
still
> > > won't do it. If I just prins the @.sql var. it looks fine.
> > > The CompanyNo field is int(4) and the Type field is varchar(30).
> > > Is there any other ways to check for an empty varchar field of can
some
> of
> > > you guide me to what it is I'm missing in my "set @.sql...."
statement?
> > >
> > > Best Regards
> > > Steen
> > >
> > >
> >
> >
>|||Sorry. It was the missing second single quote I was
referring to as missing.
Aaron is correct. You need to repeat the single quotes
since they are inside of a quoted expression.
>--Original Message--
>Hi
>Sorry if I wasn't very clear. I assume that the purpose
is to check for an
>empty field. The "original" code that's being used in
the DTS Transform task
>is "...AND CompanyNo is not NULL and Type <>'' ". It's
not me that have
>written the transform task, but I assume that this last
piece checks if
>there're any empty fields. It might be my understanding
of it that's wrong,
>but then I'd be happy to hear about it.
>This SQL statement runs fine in the DST task and also
when I run it in Query
>analyser using fixed values, but when I do it with
variables/dynamic SQL it
>seems to fail and not accept this last bit.
>Regards
>Steen
>"Matthew Bando" <anonymous@.discussions.microsoft.com>
skrev i en meddelelse
>news:071c01c46e4c$74405700$a501280a@.phx.gbl...
>> What exactly do you mean by an empty field? Generally
if
>> your field allows null and you don't set a value into
>> that field for a row, the field is set to NULL and you
>> can test for this by using "type IS NULL" in your where
>> clause.
>> "<>" is the operator for not equals and it is not a
>> singleton operator, it is a comparison operator. You
>> need something on both sides of the "<>" to compare to
>> each other.
>> For instance, if you are looking for rows where the
type
>> field is not equal to a space, you could try "type
<> ' '"
>> I hope that this helps.
>> Matthew Bando
>> bandoM@.CSCTechnologies-dot-com
>> >--Original Message--
>> >I' m having a problem with the syntax when I'm trying
to
>> run a dynamic SQL
>> >statement.
>> >
>> >The code -
>> >
>> >set @.sql = 'SELECT *
>> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
>> >
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
>> le_name+' Where
>> >Date_ >='+'2001-01-01'+'
>> > And CompanyNo Is Not NULL And Type <>'
>> >exec sp_executesql @.sql
>> >
>> >- gives me the error "Incorrect syntax near '>'." The
>> purpose of the last <>
>> >is to find the records where this field is empty
(that's
>> my understanding of
>> >it...). The basic structure of the query is from a DTS
>> Transform task, but
>> >I'm trying to "convert" this whole task to TSql.
>> >
>> >I've tried all sorts of different combinations of <>
>> and ' but it still
>> >won't do it. If I just prins the @.sql var. it looks
fine.
>> >The CompanyNo field is int(4) and the Type field is
>> varchar(30).
>> >Is there any other ways to check for an empty varchar
>> field of can some of
>> >you guide me to what it is I'm missing in my "set
>> @.sql...." statement?
>> >
>> >Best Regards
>> >Steen
>> >
>> >
>> >.
>> >
>
>.
>|||Thanks Aaron - that was also the understanding I had about using ' . I just
wanted to make sure that there wasn't anything basic about it that I had
misunderstood.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:eWVwRMmbEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Anytime a string contains ' you need to 'escape' it by doubling it. This
> tells the engine that your ' is part of the string, and should not be
> interpreted as an end-of-string marker.
> Imagine your string looks like this:
> Bob's Bait Shack
> When you put it into a string,
> SET @.string = 'Bob's Bait Shack'
> Well, where does the string end? Between the b and the s, so the rest of
> the string is lost. Except you will get an unclosed character string
error
> because other stuff follows the termination of the string.
> Try these in Query Analyzer to see what I mean:
> SELECT 'Bob's bait shack'
> GO
> SELECT 'Bob''s bait shack'
> GO
> SELECT 'Bob''s bait shack = ''''?'
> GO
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ucbpAImbEHA.252@.TK2MSFTNGP10.phx.gbl...
> > Hi Aaron
> >
> > While reading your post once more, I stumbled over your comment "..that
> > anytime you have a literal ' you must escape it...". What do you mean
> about
> > escape it? Do you just mean that I need a "start" and a "stop" '?
> >
> > Steen
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> > news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I think you're having a comprehension problem between an empty/blank
> > string
> > > (which is a valid value) and a NULL (which is an unknown/missing
value).
> > >
> > > Anyway, your syntax is wrong. It ends at ' which is the closing of
the
> > > string, so of course the EXEC call will break.
> > >
> > > DECLARE @.sql NVARCHAR(2000)
> > > SET @.sql = N'SELECT <column_list> INTO
> > '+@.db_name_dest+'.dbo.'+@.table_name+'
> > > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> WHERE
> > > Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND
> COALESCE(RTRIM(Type),
> > > '') != ''
> > >
> > > You might try getting it working as a normal statement first, then
> putting
> > > it into dynamic SQL. Remember that anytime you have a literal ' you
> must
> > > escape it so it isn't interpreted as a string terminator.
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >
> > > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > > news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > > > I' m having a problem with the syntax when I'm trying to run a
dynamic
> > SQL
> > > > statement.
> > > >
> > > > The code -
> > > >
> > > > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > > > FROM
'+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> > > Where
> > > > Date_ >='+'2001-01-01'+'
> > > > And CompanyNo Is Not NULL And Type <>'
> > > > exec sp_executesql @.sql
> > > >
> > > > - gives me the error "Incorrect syntax near '>'." The purpose of the
> > last
> > > <>
> > > > is to find the records where this field is empty (that's my
> > understanding
> > > of
> > > > it...). The basic structure of the query is from a DTS Transform
task,
> > but
> > > > I'm trying to "convert" this whole task to TSql.
> > > >
> > > > I've tried all sorts of different combinations of <> and ' but it
> still
> > > > won't do it. If I just prins the @.sql var. it looks fine.
> > > > The CompanyNo field is int(4) and the Type field is varchar(30).
> > > > Is there any other ways to check for an empty varchar field of can
> some
> > of
> > > > you guide me to what it is I'm missing in my "set @.sql...."
> statement?
> > > >
> > > > Best Regards
> > > > Steen
> > > >
> > > >
> > >
> > >
> >
> >
>|||I've now tried to play around with both my own code and the suggestion from
Aaron - but it still wont work.
When I run the code as a regular SQL statemen with fixed values, it works
fine - both my own as well as Aaron's suggestion.
When I then try to use the variables and run it as dynamic SQL, it fails
with the syntax error around the '' in the end.
I've tried as good as I can to debug the code, and it looks like when
running it as dynamic SQL it doesn't like to do the "<> '' " comparison in
the end of the statement. If I e.g. add a value so it looks like "...And Art
<> 1 " then it works. My conclusion is that when running it as dynamic SQL,
then it doesnt like to have a '' as an "indicator" of an empty field in the
end (if you get my point...).
My question is of ocurse now if any of you can suggest another way of doing
the last bit with the check of the "art" field or if there's a way to "wrap"
the Dynamic SQL statement so it understand the last '' as wanted?
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I think you're having a comprehension problem between an empty/blank
string
> (which is a valid value) and a NULL (which is an unknown/missing value).
> Anyway, your syntax is wrong. It ends at ' which is the closing of the
> string, so of course the EXEC call will break.
> DECLARE @.sql NVARCHAR(2000)
> SET @.sql = N'SELECT <column_list> INTO
'+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
> Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
> '') != ''
> You might try getting it working as a normal statement first, then putting
> it into dynamic SQL. Remember that anytime you have a literal ' you must
> escape it so it isn't interpreted as a string terminator.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > I' m having a problem with the syntax when I'm trying to run a dynamic
SQL
> > statement.
> >
> > The code -
> >
> > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> Where
> > Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> > exec sp_executesql @.sql
> >
> > - gives me the error "Incorrect syntax near '>'." The purpose of the
last
> <>
> > is to find the records where this field is empty (that's my
understanding
> of
> > it...). The basic structure of the query is from a DTS Transform task,
but
> > I'm trying to "convert" this whole task to TSql.
> >
> > I've tried all sorts of different combinations of <> and ' but it still
> > won't do it. If I just prins the @.sql var. it looks fine.
> > The CompanyNo field is int(4) and the Type field is varchar(30).
> > Is there any other ways to check for an empty varchar field of can some
of
> > you guide me to what it is I'm missing in my "set @.sql...." statement?
> >
> > Best Regards
> > Steen
> >
> >
>|||> When I then try to use the variables and run it as dynamic SQL, it fails
> with the syntax error around the '' in the end.
SHOW US!
> it doesn't like to do the "<> '' " comparison
Stop making assumptions about T-SQL's preferences, likes and dislikes,
dating habits, etc. Show us your code and we'll show you how to fix it. We
can't fix what we can't see!
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi
Here's the code - which I actually now have got working ...:-).
set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' Where
Date_ >='+'2001-01-01'+'
And CompanyNo Is Not NULL And Type <>''
exec sp_executesql @.sql
What I was missing, was the last two ' ( which I think was what Aaron was
indicating...). I was rest assured that I had tried that earlier without
getting it working, but I must be wrong.
Thanks for all your efforts in helping me....At least all this lead me to
the article written by Erland Sommerskog about Dynamic SQL plus what Aaron
has written about it on aspfaq.com. That helps understanding and knowing
Dynamic SQL a bit better.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:uMoRcGybEHA.252@.TK2MSFTNGP10.phx.gbl...
> > When I then try to use the variables and run it as dynamic SQL, it fails
> > with the syntax error around the '' in the end.
> SHOW US!
> > it doesn't like to do the "<> '' " comparison
> Stop making assumptions about T-SQL's preferences, likes and dislikes,
> dating habits, etc. Show us your code and we'll show you how to fix it.
We
> can't fix what we can't see!
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>

Wednesday, March 28, 2012

Problem with JOIN

Monday, March 26, 2012

Problem with insert Time to database

Wednesday, March 21, 2012

Problem with HTML slightly garbled sending email as web archive

Problem with Having and Max

Problem with GROUP BY syntax and expression

Problem with group by

Tuesday, March 20, 2012

problem with functions and datetime parameters!

Monday, March 12, 2012

problem with executing an sqlcommand

Problem with EXEC xp_cmdshell in UDF's

Friday, March 9, 2012

Problem with embedded code In REporting Services 2005

Problem with dynamic SQL syntax

Problem with dynamic SQL syntax

Problem with dynamic SQL syntax

db datatypes

Blog Archive

About Me