Re: SQL query optimization - Database Discussion Boards

Re: problem while import TXT file

_Damian S_9-Dec-09 20:23

_Damian S_

9-Dec-09 20:23

Create the table first with the correct sizes, then import the text file into your table.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Re: problem while import TXT file

koolprasad200310-Dec-09 22:24

koolprasad2003

10-Dec-09 22:24

thanks for reply damian

i have done same thing as u told above but still i have to go in advanced import option and correct datatype and size for all columns.

how do u import your txt files ??? or is there any option for the same ???

thanks in advnace
koolprasad2003 Smile | :)

Rating always..... WELCOME

Be a good listener...Because Opprtunity knoughts softly...N-Joy

Table result to Xml

Fayu9-Dec-09 5:31

Fayu

9-Dec-09 5:31

Hi,

I am trying to convert a table result to a xml. For example, I have a result (Figure 1) and I want to make an xml out of it (Figure 2).

I have tried the FOR XML RAW and FOR XML AUTO but this does not seem to give me the tree structure. Any ideas?

Figure 1:

Parent     Id     Name
-----------------------
0          1       Root
0          2       Root 2
1          3       Child 1
1          4       Child 2
3          5       Child 3
4          6       Child 4
9          7       Child 5

Figure 2:

<NodeCollection>
   <Node rId=1 Parent=0 Name='Root'>
      <Node rId=3 Parent=1 Name='Child 1'>
          <Node rId=5 Parent=3 Name='Child 3' />
      </Node>
      <Node rId=4 Parent=1 Name='Child 2'>
          <Node rId=6 Parent=4 Name='Child 4' />
      </Node>
   </Node>
   <Node rId=2 Parent=0 Name='Root 2' />
   <Node rId=7 Parent=9 Name='Child 5' />
</NodeCollection>

Re: Table result to Xml

Riaan Booyzen9-Dec-09 20:05

Riaan Booyzen

9-Dec-09 20:05

Hi Fayu,

Have a look at trying for xml explicit, try this article for some help http://www.eggheadcafe.com/articles/20030804.asp[^]

Hope you get it working

SQL query optimization

Member 46483709-Dec-09 1:59

Member 4648370

9-Dec-09 1:59

Hi All,

Thanks for those who take an interest in reading posts like these - and I sincerely hope that at least one of you can help me.

I am fairly new at SQL (without formal trianing...) so this might be very noob.

I have inherited the maintenance of a oldish software project. I am only getting to know the code and there is lot of it, so I feel a bit desperate. OK OK I'll get to the point!

My client needs me to solve a problem ASAP. They send out mail merge letters frm the application and they have a very sophisticated piece of code that allow you to filter the client database according to many criteria. When the criteria is entered it generates an integer (iParam below) that is used to filter a few tables. We use a autogenerated SQL statement for every report to populate it. The SP looks like this:

@iParam varchar(100)  AS 
SELECT 
ipkClientsID AS [ID], 
tblClients.sClientName AS [Client: Client Name], 
(SELECT CONVERT(varchar(8000), sTextValue) FROM tblReportSummary WHERE       ifkID = tblClients.ipkClientsID AND       iParam = @iParam AND       sFieldName = 'Client: Postal Address Group (R)')  AS [Client: Postal Address Group (R)] FROM tblClients 

WHERE ipkClientsID IN (SELECT ifkSelectedItemsID from tblSelectedItems where sID = @iParam)

as you can see the select statement is quite simple BUT for some reason the performance has gradually deteriorated over time to the point where every report that runs, times out or crashes the application (?).

I am hoping that you can take one look at it and say: That won't work well - try THIS!, as it might be a simple way of improving the select statement.

Much obliged!!! for any help or comments.

regards
Hawk

Re: SQL query optimization

WoutL9-Dec-09 2:30

WoutL

9-Dec-09 2:30

You could try

SELECT c.ipkClientsID AS [ID], c.tblClients.sClientName AS [Client: Client Name], 
	CONVERT(varchar(8000), sTextValue) AS [Client: Postal Address Group (R)] 
FROM tblClients c
Left join tblReportSummary r on r.ifkID = c.ipkClientsID 
	AND       r.iParam = @iParam 
	AND       r.sFieldName = 'Client: Postal Address Group (R)'
WHERE c.ipkClientsID IN 
(
	SELECT ifkSelectedItemsID 
	from tblSelectedItems where sID = @iParam
)

But I think you can best look at the Indexes on the tables.

Wout Louwers

Re: SQL query optimization [modified]

Member 46483709-Dec-09 3:02

Member 4648370

9-Dec-09 3:02

Thanks Wout,

I will try your advice.

The indexes was the first thing I checked. There are indexes on both tables for all related fields - I was hoping for a quick fix there, alas my predecessor had already tried that.

I have now run through the code and it is definitely this stored proc that causes the problem. I am going to replace it with your statement and see what happens.

regards
Holger

modified on Wednesday, December 9, 2009 9:37 AM

Re: SQL query optimization

Member 46483709-Dec-09 3:47

Member 4648370

9-Dec-09 3:47

30 mins or so later...

Hi Wout,

OK your revised query worked (apart from minor error in line 1).

BUT - and this is interesting - I thought I had this one licked.

However I just ran Wout's suggested statement natively in SQL Manager and it produced results very quick. I tried a different parameter (different report) and it also worked well.

I then tried to run it from code - which actually calls a SP that contains the query as Wout suggested... and ALAS it takes forever...

I tried the original statement in SQL (not from SP) and it works very quick as well.

I then called the SP from SQL manager using "EXEC sp_...." and it also took forever.

It would appear that calling this transaction from a SP dramatically slows it down.

Why is this?

Any other good suggestions?

Re: SQL query optimization

WoutL9-Dec-09 20:46

WoutL

9-Dec-09 20:46

Could you look at the differences between the execution plans of the query and the SP?

Wout Louwers

Re: SQL query optimization

_Damian S_9-Dec-09 19:24

_Damian S_

9-Dec-09 19:24

If the table tblSelectedItems is quite large, you would be better off linking it with an inner join, like this:

replace this bit:

Member 4648370 wrote:
WHERE ipkClientsID IN (SELECT ifkSelectedItemsID from tblSelectedItems where sID = @iParam)

With this:

inner join tblSelectedItems on ((c.ipkClientsID = ifkSelectedItemsID) and (sID = @iParam))

system error code 126

T21029-Dec-09 1:21

T2102

9-Dec-09 1:21

I try to access the DSN settings for an Oracle database and I get the following errors:

The setup routines for the Oracle in OraClient11g_home1 ODBC driver could not be loaded due to system error code 126.
Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed!
Could not load the setup or translator library

Is this fixable? What is the problem?

The following microsoft support article did not help unfortunately...

http://support.microsoft.com/kb/260558[^]

"Syscomments" system table corrupted

www.Developerof.NET9-Dec-09 0:28

www.Developerof.NET

9-Dec-09 0:28

Hi,

I have SQL 2000 installed on my development machine. Things were fine till yesterday, but then things started turning out nasty.

I created an SP with name sp_retrieve_ageinginward_details ( I am aware that naming SPs with "sp_" as a prefix is not good ). Today just to make a minor change i scripted the SP, but it returns an error:

---------------------------
SQL Query Analyzer
---------------------------
[SQL-DMO]Object 'dbo.sp_retrieve_ageinginward_details' was not scripted. It is possible that the information in system table 'syscomments' about the object was corrupt or missing.
---------------------------
OK   
---------------------------

Also when i check the script from syscomments it gives me an error in an C++ file.

Server: Msg 3624, Level 20, State 1, Line 1
 
Location:	 recbase.cpp:1371
Expression:	 m_nVars > 0
SPID:		 57
Process ID:	 556

Connection Broken

How can i retrieve my SP from my DB Sigh | :sigh:

, since its huge and i have no backup. Cry | :((

Pls help.......

When you fail to plan, you are planning to fail.

Re: "Syscomments" system table corrupted

Ashfield9-Dec-09 1:17

Ashfield

9-Dec-09 1:17

If you can see it in object explorer try right click and that will give you an option to script it. Other than that, you may be able to pull the info out of syscomments by direct sql (id joins to id from sysobjects)

If this fails, then you have learnt the lesson - always save your sql to a flat file and put in in a vserion control system.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

SQL Copy Table in C#

bxlorenz8-Dec-09 13:40

bxlorenz

8-Dec-09 13:40

I have a C# program that has a connection to 2 different databases across 2 different SQL servers.

How can I easily copy a table and all its contents from one database to another using SQL statements in C#

I do not want to use the management studio to perform this and want to do it in my C# program.

Any help would be appreciated.

Thanks

Re: SQL Copy Table in C#

loyal ginger8-Dec-09 15:49

loyal ginger

8-Dec-09 15:49

This may be what you are looking for:

http://69.10.233.10/KB/database/GenericCopyTableDataFcn.aspx[^]

I used his solution in my project. It worked well.

Happy programming!

Re: SQL Copy Table in C#

Niladri_Biswas8-Dec-09 21:36

Niladri_Biswas

8-Dec-09 21:36

Solution 1:

Take the help of

SSIS

for the task. It is handy and simple.

Solution 2:

Using

SMO class

, you can create a backup of your database and restore it also.

There are plenty of articles in the net explaining how to do the same using SMO.

Please have a look

Backup and restore SQL databases[^]

Hope this helps
Smile | :)

Niladri Biswas

Re: SQL Copy Table in C#

Paul Voicu10-Dec-09 2:06

Paul Voicu

10-Dec-09 2:06

Try using SqlBulkCopy - google for that and check out the example here http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server[^]

INSTEAD OF trigger

Member 47041438-Dec-09 13:34

Member 4704143

8-Dec-09 13:34

Hi all,

I want to create a trigger relating to table Demo and table UpdateInfo. UpdateInfo is created as following:

CREATE TABLE UpdateInfo
(ID				int			primary key,
new				int,
click			int)

The task for this trigger is whenever there is a row inserted to Demo, there is an update in field [new] of UpdateInfo ( new = new +1) , but it wll just work, for example, from 1:00 am to 6:00 am.

If this has been done, I then want to customize the time from an aspx page.

I think I should start with INSTEAD OF trigger but I am lacking in experiences about it.

Could you give me any suggestion?

Thank you so much!

Re: INSTEAD OF trigger

David Skelly8-Dec-09 23:43

David Skelly

8-Dec-09 23:43

How is this different from your question yesterday about triggers in a certain time period?

Re: INSTEAD OF trigger [modified]

Member 47041439-Dec-09 0:30

Member 4704143

9-Dec-09 0:30

yep,

But the answer I got yesterday is still dubious. I am still having no ideas about how to check time & check by which syntax.

The question today is one in my effort to find out solution for the question of yesterday, but in a different form Big Grin | :-D

I think INSTEAD OF trigger can help me to sovle prolem. That is the reason for the question today ^^

However, if you think just a standard trigger is quite enough to solve my problem, could you give me a more specific example?

Thanks for your help!

modified on Wednesday, December 9, 2009 8:36 AM

sqlserver apostrophe problem.

gerrybrennan7-Dec-09 20:54

gerrybrennan

7-Dec-09 20:54

Hi
I have a stored procedure schemacreate.sql and below is a snippet.

I am having problems with creating the below procedure. More specifically

and TransType = "SI"

Can any one help me with this.

Thank you in advance.

G

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF



IF NOT EXISTS (select * from sysobjects where id = object_id(N'[dbo].[TransCheckifCurrentExists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[TransCheckifCurrentExists]

as 
declare @SQLstr nvarchar(1000)
declare @value int;



set @SQLstr = ''select  @value=count(*) from Trans where TransState = 1 and TransType = "SI"  ''


exec sp_executesql @SQLstr, N''@value int out'', @value out
return


'
END

Re: sqlserver apostrophe problem.

Ashfield7-Dec-09 20:56

Ashfield

7-Dec-09 20:56

Double quotes are not valid to enclose literals, you need single quotes. I would suugest before executing your sql string you print it (at least for debug) as it soon shows any syntax errors.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: sqlserver apostrophe problem.

gerrybrennan7-Dec-09 21:14

gerrybrennan

7-Dec-09 21:14

Hi Bob,

Thank you for your help.

When I use single quotes i get an error

Line 16: Incorrect syntax near 'SI'.

G

Re: sqlserver apostrophe problem.

Niladri_Biswas7-Dec-09 21:42

Niladri_Biswas

7-Dec-09 21:42

Try

and TransType =

''SI''

Niladri Biswas

Re: sqlserver apostrophe problem.

Nilesh.Kuvar7-Dec-09 23:28

Nilesh.Kuvar

7-Dec-09 23:28

Hello,

Generally here, apostrophe is considered as escape sequence character
You may execute as

TransType = ''SI''

This may solve you problem
Thumbs Up | :thumbsup:

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.