|
GuyThiebaut wrote: IMHO from a programming side it is always more beautiful to use joins than lots of temporary tables - if you can manage without temporary tables.
Thanks, But I heard if we have several joins, it can be splitted into temporary table which can boost the performance. And I think performance depends on the situation where we use temporary tables. I guess performance would be costly when it is used for indexing. Because it needs more effort for recompilation.
|
|
|
|
|
The key sentance from the article that you reference is:
While temporary table may sometimes be unavoidable, they can often be sidestepped by using derived tables instead.
The article doesn't mean that temporary tables should never be used - only that the author has noticed that some programmers over-use temporary tables when other methods would be more efficient.
|
|
|
|
|
hi
have u tried ny Query?
Regards
Joe
|
|
|
|
|
hi
i dint get your problem ..
are you looking for a qry which returns the names of the society with a specific society type.
fot that you can use the below qry
select society_name from test_society where society_type=1
Regards
Joe
|
|
|
|
|
declare @exp varchar(100)
set @exp = '12,13,14'
declare @start int
declare @pos int
declare @substring varchar(20)
set @pos = 1
set @start = 1
while(@pos <> 0)
begin
set @pos = charindex(',' , @exp , @start)
if(@pos = 0)
begin
print substring(@exp , @start , len(@exp))
return
end
print substring(@exp , @start , @pos -1)
set @start = @pos + 1
end
i want the following output
12
13
14
But the output is
12
13,14
14
please rectify it
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Is there any way you can take your input as XML and use OpenXML instead of this nasty string mashing ?
'
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
this is the database side processigs.actually i don;t konw , how long the string can be , 12,13,14,15.......there in the database i am saving these numbers.problem is the code i am using at database end , shows ths following output
12
13,14
14
for the 12,13,14 expression.
i wanted
12
13
14
so far xml interaction is not taking place .
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Can u try the following:
-- Code
declare @ExpList varchar (1000)
, @Delimiter char (1)
, @INTValue varchar (20)
, @Position int
begin
create table #Result
(
IntValue int
)
set @ExpList = '10, 11, 12, 13, 14'
set @Delimiter = ','
set @ExpList = ltrim (rtrim (@ExpList)) + @Delimiter
set @Position = charindex (@Delimiter, @ExpList, 1)
if replace (@ExpList, @Delimiter, '') <> ''
begin
while @Position > 0
begin
set @INTValue = ltrim (rtrim (left (@ExpList, @Position -1)))
if @INTValue <> ''
begin
insert into #Result (IntValue)
values (cast (@INTValue as int))
end
 set @ExpList = right (@ExpList, len (@ExpList) - @Position)
set @Position = charindex (@Delimiter, @ExpList, 1)
end
end
select * from #Result
truncate table #Result
drop table #Result
end
go
Sample OUTPUT:
--------------
IntValue
--------
10
11
12
13
14
-- End of Code
Kishore.P
|
|
|
|
|
What's the problem in the following
declare @exp varchar(100)
set @exp = '12,13,14'
declare @start int
declare @pos int
declare @substring varchar(20)
set @pos = 1
set @start = 1
while(@pos <> 0)
begin
set @pos = charindex(',' , @exp , @start)
if(@pos = 0)
begin
print substring(@exp , @start , len(@exp))
return
end
print substring(@exp , @start , @pos -1)
set @start = @pos + 1
end
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Modified your code, see the below code:
declare @exp varchar(100)
, @start int
, @pos int
, @substring varchar(20)
set @exp = '12,13,14'
--set @pos = 1
set @start = 1
set @pos = charindex(',' , @exp , @start) -- Added here
while(@pos <> 0)
begin
--set @pos = charindex(',' , @exp , @start)
if(@pos = 0)
begin
print substring(@exp , @start , len(@exp))
return
end
print substring(@exp , @start , @pos -1)
set @pos = charindex(',' , @exp , @start) -- Added here
set @start = @pos + 1
end
go
Kishore.P
|
|
|
|
|
Sonia Gupta wrote: print substring(@exp , @start , @pos -1)
while executing this statement
1st time, @start is 1 and @pos is 3. so 12 is printed
2nd time, @start is 4 and @pos is 6. so 13,14 is printed i.e. from 4th position 6 characters are printed
Regards
KP
|
|
|
|
|
yes.ThanQ
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
|
Hello
I want to store the date in the sql DataBase like "11/15/2006"
i can't use the char or varchar because i need to do a filter on it, and if i choose smalldatetime it will stored like "11/15/2006 12:00:00 PM"
how i can do ?
thanks in advance
Assaf
|
|
|
|
|
Assaf82 wrote: I want to store the date in the sql DataBase like "11/15/2006"
i can't use the char or varchar because i need to do a filter on it, and if i choose smalldatetime it will stored like "11/15/2006 12:00:00 PM"
No, it isn't stored like that. You are confusing storage formats with presentation formats. It is stored as a 32 or 64 bit number in a way that is efficient for querying. The presentation format should normally be done by the application using the database rather than the database itself.
Upcoming FREE developer events:
* Developer! Developer! Developer! 6
* Developer Day Scotland
My website
|
|
|
|
|
G'day folks,
I have been experiencing an extremely strange problem in my code, this has to be marked as a X-File episode.
I have a method in my class which executes two other methods. One method(Method A) inserts a row in table A and the other (Method B) inserts a row in table B. This is part of an asp.net application.
When I run the the asp.net application without debugging both methods (A and B) execute fine, however method B doesnt seem to be updating table B whereas Method A updates Table A just fine.
I ran my query through MS Access with test data and the table got updated. Then I decided to step through the code and again method B seems to be updating table B.
I run the application again without debugging and samething happens Table B doesnt get updated and Table A is getting updated.
For the life of me I cant seem to point my finger where the problem is residing. Can this be a connectivity issue with MS Access and my asp.net application ? Both the asp.net and access database are residing in my development environment.
Any pointers will be appreciated.
thanks
|
|
|
|
|
ekynox wrote: Any pointers will be appreciated.
Here ya go![^]
Seriously, you might want to also put in some tracing code so that you can see exactly what is being sent to the database (or if the method is being called)
Or perhaps there is something obvious in the method itself that is different from the other method.
Upcoming FREE developer events:
* Developer! Developer! Developer! 6
* Developer Day Scotland
My website
|
|
|
|
|
LOL Colin that's one funny pointer.
I managed to work out my problem, it was do with timing issue. By the time MethodA finishes executing, I believe access has not had time to update Table A, then MethodB starts executing and Table A is missing the relavant data and MethodB fails to insert a row in Table B.
Why does it work in debug mode well when debugging there is ample time for Access to actually commit the data into the table.
Solution was to a a Thread.Sleep() to cause pause and it all works.
Yes you are right about placing tracing code.
<br />
public void AddStuffToDB()<br />
{<br />
MethodA(string x,string y);<br />
<br />
MethodB(string x);<br />
}<br />
|
|
|
|
|
Hello,
i have a ms access database, there is information written in cyrillic encoding. When i read the text from c++ using Ado, it cannt read proper text, it reads only "???????????????", what should i do to read correct text in c++?
here is the code
<br />
CString strItem;<br />
strItem = (char *) (_bstr_t) pRecordPtr->Fields->GetItem("name")->Value;<br />
strItem is "?????" if when encoding is cyrillic.
OR
Is there sql command like "SET CHARACTER SET utf8" in Jet Sql, Mysql has the command, but wondering that Microsoft Jet sql has it?
if Microsoft Jet Sql has it, it would be better to change encoding
thanks
|
|
|
|
|
Hello everyone.
I h've been trying for the last week to access a variable in my SSIS package and foward it down to a Data Conversion Item. I've read that I could use a Derived Column or a Script Component Item but I have not had any luck. When I use the Script Componet I am able to test the variable value by adding the code Msgbox(Me.Variables.WMWeekNbr) in the method CreateNewOutputRows in the ScriptMain class. I see the message box with value but it doesn't foward to the data conversion item. I did add the variable to ReadOnlyVariables property. I also created a output columns on the script object.
Im basically trying to create a C# application that will take a value and pass it to the SQL SSIS package which will delete values from the database.
Thanks 
|
|
|
|
|
I am using SSRS 2005 for the remote access of the reports. I will put url on browser, this will show the result. I am using ListChildren method.
catalogItems = rService.ListChildren(Global.ReportPath,true);
But the code is not working. This will make the tree. This should retrieve no. of reports from report server remotely and put in array, catalogItems. Based on the number of reports the another loop will retrieve names of the reports and will populate the tree vi ew.
Help me please.
|
|
|
|
|
Why is it not working? Do you get an exception? If so, what is the message?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Now I am able to populate the reports remotely.
I have used following lines :
rService.Url = "http://10.10.40.2/ReportServer/ReportService2005.asmx";
before,
catalogItems = rService.ListChildren(Global.ReportPath, true);
and added web references.
But I do'nt know how to do role based forms authentication. I need help.
|
|
|
|
|
This is a different question to the one you began the thread with. You should really start a new thread for it.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I have recently mnodified the security arrangements of my ASP.NET application and am now receiving the error
"The Microsoft Jet database engine cannot open the file '\\W91\AvolitesDownload\Databases\2008License.mdb'. It is already opened exclusively by another user, or you need permission to view its data"
I have got my self in a bit of a tangle over this and now can't work out how it used to work!
Application details:
ASP.NET pages(C#)
one SQL Server 2005 database which I can read/write to fully and correctly
one MS Access 2003 database for which I now receive the above error
connection strings for both are stored in the web.config, a sample of which is below:
<code>
<configuration>
<connectionStrings>
<add name="W123_LicensingSourceData" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source W123\Databases\Copy of 2008License.mdb;Persist Security Info=True;" providerName="System.Data.OleDb" />
<add name="LicensingConnectionString" connectionString="Data Source=s321;Initial Catalog=Licensing;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true"/>
<authentication mode="Windows"/>
<identity impersonate ="true"/>
</system.web>
<location path="Operator">
<system.web>
<authorization>
<allow users="DOMAIN\Reception"/>
<allow roles="SoftwareDeveloper"/>
<deny users="*"/>
</authorization>
</system.web>
</location>
<location path="Admin">
<system.web>
<authorization>
<allow users="Reception"/>
<allow roles="SoftwareDeveloper"/>
<deny users="*"/>
</authorization>
</system.web>
</location>
<location path="Production">
<system.web>
<authorization>
<allow users="DOMAIN\Production"/>
<allow roles="SoftwareDeveloper"/>
<deny users="*"/>
</authorization>
</system.web>
</location>
</configuration>
</code>
The windows authentication seems to be the correct way forward, and "Everyone" is allowed full access to the MSAccess database. There is no user name or password needed to open the DB itself. The ASP.NET app discussed above is on a different server to the Access DB. A second ASP.NET which is hosted on the same server as the Access DB has no problem with connecting to it.
Any help for the above much appreciated
Thanks, Des
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
