Database

Re: SQL Issue during database creation

27-Jun-09 6:09

I don't see anything wrong, but that may be because I didn't look closely since you didn't use PRE tags which would improve readability.

The good thing is you were wise to choose multiple lines, so you can easily remove part of the statement and discover which part is causing the trouble.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

Jerry Hammond28-Jun-09 17:19

Jerry Hammond

28-Jun-09 17:19

engbahramZarin wrote:
"[strNew] NUMBER(1)," & _

Is this really a string datatype, or are you naming it as a string even though you are declaring it a number type just to keep the guys on their toes?

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Where the SSIS package saved in SQL2005

Aman786Singh27-Jun-09 3:39

Aman786Singh

27-Jun-09 3:39

Can any one tell me where the SSIS package saved in SQL server2005 Standard edition.???

I made one import package , but i don't know where it is saved in SQL2005.

Thanks in Advance.
Aman

Re: Where the SSIS package saved in SQL2005

Niladri_Biswas27-Jun-09 17:13

Re: Stored procedure help

27-Jun-09 17:13

Depending on your deployment, they are generally stored either on MSDB database or in the file systems.

I basically use VS 2008 and MSSQL2005 and the SSIS packages are stored in

drive name:\Documents and Settings\My Documents\Visual Studio 2005\Projects

Or else you can also look into thru the defined projects path in Tools>Options of Visual Studio

Anyway, for more details on

How to: Save a Package to the File System visit this link
[ http://msdn.microsoft.com/en-us/library/ms137970.aspx[^] ]

How to: Save a Copy of a Package in MSDB database

[ http://msdn.microsoft.com/en-us/library/ms137565.aspx[^]]

Hope this helps
Smile | :)

Niladri Biswas

Stored procedure help

paypony27-Jun-09 1:34

paypony

27-Jun-09 1:34

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[Usp_D_et_Login1234]
@name nvarchar(max),
@pwd nvarchar(100),
@type nvarchar(5)

as
begin
if @type='1'
begin
select DId,LoginName from dd_login where LoginName=@name and Password=@pwd
end

if @type='0'
begin
select RId,LoginName from dr_login where LoginName=@name and Password=@pwd
end

end

Is this stored procedure can be injected?
Whether sql injections can be apply on this procedure?
If yes tell me how ?
and also how to recover from it? In sqlserver2005.(backend)(asp.net frontend)

Niladri_Biswas27-Jun-09 6:05

Fetching dependent records from the same table

27-Jun-09 6:05

Hi man,

I would like to shed some light on this topic.

As far as I know, SOL injection happens in the parameters that we are sending to the stored proc and not inside the stored proc.

And the most venerable datatypes are the strings.

It is always recommended to put a check on the string data types.

However, the risk of sql attack can be minimised if you are using the

SQLCommand and SQLParameter ADO/ADO.NET objects.

For more knowledge, I recommend you to go through these tutorials

1) http://msdn.microsoft.com/en-us/library/ms998271.aspx[^]

2)http://www.devcity.net/Articles/250/1/article.aspx[^]

Regarding your question as how to protect injection from asp.net, besure about one thing, that using javascript, sql injection is highly possible.

Look into this article

http://kb2.adobe.com/cps/581/581a553c.html[^]

For a precise example that you posted here, a good answer can be found here
http://www.sitepoint.com/article/sql-injection-attacks-safe/2/[^]

Hope this helps.
Smile | :)

Niladri Biswas

Neeraj Kr26-Jun-09 7:12

Neeraj Kr

26-Jun-09 7:12

Hello, I am new to databases. I need urgent help. I have a table named Category, which contains 4 fields - categoryid, siteproprtyid, topCategoryId.

the topcategoryid contains the categoryid of the higher level category. Likewise there are four level of category. An example to this is-

categoryid sitepropertyid topcategoryid
---------- -------------- -------------
1 16 Null
2 16 1
3 16 2
4 16 3
5 16 1

I want the output to be like

Category1 Category2 Category3 Category4
--------- --------- --------- ---------
1 2 3 4
1 5

Please help me regarding this

Re: Fetching dependent records from the same table [modified]

Niladri_Biswas26-Jun-09 15:54

26-Jun-09 15:54

Hi, your question is not so clear.

As of now what I understood, you need a row column transposition which can be achieved using PIVOT function of SQL SERVER .

You are saying that there are 4 level of categories.

In the output, that you presented here, I cannot find any sort of link between the first and second rows.

What is 5 doing in the second row? How is it related in this context? Does 1 will always appear in the Category1?

Is this 1, the TOPCATEGORYID and is it a demarcation indicating for a next row?

Category1 Category2 Category3 Category4
--------- --------- --------- ---------
1 2 3 4
1 5

Explain everything clearly... then it will be easier to answer your problem.

Smile | :)

Niladri Biswas

modified on Friday, June 26, 2009 10:14 PM

Neeraj Kr26-Jun-09 19:45

Neeraj Kr

26-Jun-09 19:45

Hi, yea may be I was not able to put my question properly. Let me try once more. Suppose CategoryId 1 is the root level, so thete is no top level category for it and it is null. The CategoryId 2 is the child of CategoryId 1, therefore, the topcategoryid for Category 2 is 1. Like wise for Category 3, the topcategoryid is 2 and for CategoryId 4, the topcategoryid is 3. CategoryId 5 is the child of CategoryId 1 and there are further no child categories under it.

Therefore, I showed thebase table like that. For the same, I want the output in the manner I depicted.

I hope I am able to explain this time.

Thanks for bearing with me.

-----Have A Nice Day-----

Mycroft Holmes27-Jun-09 0:37

Mycroft Holmes

27-Jun-09 0:37

If you are using SQL Server 2008 then look into the hierarchy ID.

You actually have 2 problems, getting a reasonable hierarchy and then pivoting the result set. This may be of some use[^].

Never underestimate the power of human stupidity
RAH

Niladri_Biswas27-Jun-09 16:05

27-Jun-09 16:05

Sorry for being late in answering your question.
I was completely bogged up with my project.

However, please try this

SELECT F.CATEGORY1,F.CATEGORY2,F.CATEGORY3,F.CATEGORY4 

FROM(

SELECT	C1.TOPCATEGORYID AS CATEGORY1,
		C1.CATEGORYID AS CATEGORY2,
		C2.CATEGORYID AS CATEGORY3,
		C3.CATEGORYID AS CATEGORY4

FROM  CATEGORY AS C1
LEFT OUTER JOIN  CATEGORY AS C2
ON  C2.TOPCATEGORYID = C1.CATEGORYID
LEFT OUTER JOIN  CATEGORY AS C3
ON C3.TOPCATEGORYID = C2.CATEGORYID
WHERE C1.TOPCATEGORYID IS NOT NULL) F 

WHERE F.CATEGORY1  = 1

N.B.~ The inner derived query is the right one. It is showing the right dependencies. Since I had to bring precisely to your format, so I applied the outer query.

However, it will work as per the input you gave and as per your desired output

Let me know in case of any concerns.
Smile | :)

Niladri Biswas

Re: Fetching dependent records from the same table [modified]

Neeraj Kr27-Jun-09 16:42

Neeraj Kr

27-Jun-09 16:42

Hi,

I did not run the query, but as a dry run, it looks to me absolutely perfect. On Monday, after reaching office, I will try it and will send you the response. Can you kindly send me ur email id.

Thanks

-----Have A Nice Day-----

Niladri_Biswas27-Jun-09 16:59

Develop own small database-system

27-Jun-09 16:59

Dear Neeraj,

I have tested that query and with varying inputs. It worked fine in all the situations.

The inner query is doing the necessary job. It is bringing out all the dependent records.

I wrote the outer query only to satisfy your requirement.

But anyways, I enjoyed to solve this kind of queries.

By the way, you can always mail me by clicking on the email link provided here in the message section
Smile | :)

Niladri Biswas

modified on Saturday, June 27, 2009 11:16 PM

softwarejaeger26-Jun-09 6:21

26-Jun-09 6:21

Hello,

i'm searching a few weeks for a database which fits to my requirements and where i don't have to pay any cent. MySQL is very great, but i don't know what's with the licenses, do i need to pay for the db-system or only for the connector or not and so on...

So i wanted to ask, is it a good idea to create a "own" database? Maybe, for each Table an own, maybe XML-File or CSV-File? I don't need a lot of relations and so on, it must be only fast. And it mustn't be very secure, because only a server is talking to my DB over TCP. The hole thing user-management, or index-keys and so on, that's coded very fast, and that's not the problem. The problem is, how to save that files? I mean, how does a database load their data? I don't access for each SELECT-Command the HDD.

So how to make it, i have a lot of ideas, but i don't know if it's makeable. Or maybe could somebody explain how a DB-System like MySQL works? They have all their DB-Table-Files soemwhere on the filesystem.

Many thanks.

Luc Pattyn26-Jun-09 6:55

26-Jun-09 6:55

Hi,

two suggestions:

1. for single-user applications, you could use Access; from a .NET perspective all this takes is an MDB file; once created (requiring Access as part of Office), you no longer need MS Access itself. Drawback is it has some functional limitations, and offers an SQL subset.

2. SQL Server Express is downloadable and free; it supports a limited number of users (10?) and a limited DB size (4GB?)

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

softwarejaeger26-Jun-09 7:04

26-Jun-09 7:04

Well that's the problem, i don't know how many users will use my database Smile | :)

It could be only 10 or maybe 1000 till 5000 Smile | :)

And i have a lot data to save Smile | :)

Not big but many, it should be used on a webservice or a web-application.

Luc Pattyn26-Jun-09 7:08

26-Jun-09 7:08

Have a look then at what your web host is offering.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

softwarejaeger26-Jun-09 7:14

26-Jun-09 7:14

It's my own server/s, or dedicated root servers.

So i "easily" need to storage data (a lot of data) and must access them pretty fast. Is there a way to do it on my own? What's with DataSet and XMLDataDocument? How is there the performance?

Luc Pattyn26-Jun-09 7:18

26-Jun-09 7:18

I can't tell you from my own experience, I only use a small MySQL db.
Most people here seem to favor SQL Server (the non-Express one for real applications).

This is something I copied here from earlier this year:

16-JAN-2009 Mika Wendelius, on choosing a database
- for single user application with small amount of local data => SQL Server Compact Edition
- for multi user application with medium amount of shared data => SQL Server Express / Oracle XE
- for multi user application with large amount of shared data => SQL Server Std or Enterprise / Oracle 11g

I believe that the next question you would have is: what is a small or large amount of data. Again a matter of opinion but perhaps some quantities (these are certainly not accurate but give some idea):
- small amount could be less than 1 GB, perhaps less than a hundred tables and no table contain over 10 000 rows
- large amount could be more than 4 GB of actual data, perhaps 500+ tables tables and and some tables contain over 10 000 000 rows. User amount is high, let's say over 1000 concurrent.

And maybe this could be useful:
Comparison of relational database management systems
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems[^]

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.

softwarejaeger26-Jun-09 7:34

26-Jun-09 7:34

Hm OK, i think that my DB really wouldn't have more than 4GB this should really be enough.
My idea was, if i use a db-system, i had the idea to make run it on linux. So MS-SQL isn't the right DB for me.

Web Apps only have 1 user

David Mujica26-Jun-09 7:21

David Mujica

26-Jun-09 7:21

Don't waste your time trying to develop a data storage engine. There have been hundreds of man-years invested in developing database engines. MS-Access is really quite fast and the license for MS-SQL server is really not that bad, if this is going to be a professional web application.

You speak of "clients" right ? Then they are paying a fee, right? Don't cheap out. Consider it an investment.

See my other post about starting with MS-Access and migrating to a more robust DB when you need it.

David Mujica26-Jun-09 7:16

David Mujica

26-Jun-09 7:16

If it is a web application, then you could argue that there is only 1 user making an actual connection to the database; the web app itself! Remember that this is not a client server application where you need to create a user logon for everyone who wants to access the database.

Hmmm ... things to think about.

Consider this, it sounds like you really don't know how much data you are going to store or how frequently it will be accessed, so why don't you start with MS-Access as the database and make sure that you design you application in such a manner that it would be relatively easy to change database enginges without causing a massive re-write. It can be done. Make sure you have a good data abstraction layer designed and you could actually have your application capable of running on all kinds of database engines, MS-SQL, MySQL, Oracle. It all starts with the design.

Just a thought. Cool | :cool:

Re: Web Apps only have 1 user

softwarejaeger26-Jun-09 7:27