Database Discussion Boards

Deleting a row

Suresh R..3-Sep-07 22:26

Suresh R..

3-Sep-07 22:26

Can you please tell me how to delete a perticular row Confused | :confused:

from a table in SQL Server 2005?

Confused | :confused:

Re: Deleting a row

Blue_Boy3-Sep-07 22:38

Blue_Boy

3-Sep-07 22:38

delete from tablename where id in(1,4,6)
this sql query deletes row 1,4 and 6

I Love SQL

Re: Deleting a row

John-ph4-Sep-07 0:51

John-ph

4-Sep-07 0:51

Suresh R.. wrote:
how to delete a perticular row from a table in SQL Server 2005

you can have this...

--Deleting single or multiple rows

DELETE FROM TABLE WHERE ID = VALUE

DELETE FROM TABLE WHERE ID IN (VALUE1, VALUE2, VALUE3)

--deleting top 100 rows

DELETE FROM TABLE WHERE ID (SELECT TOP 100 ID FROM TABLE ORDER BY ID)

--delete records in one table based on values in another table

DELETE FROM TABLE A
WHERE EXISTS
  ( SELECT ID
     FROM TABLE B
     where B.ID = A.ID)

--delete records in one table based on values that doesn't match in another table

DELETE FROM TABLE A
WHERE NOT EXISTS
  ( SELECT ID
     FROM TABLE B
     where B.ID = A.ID)

--deletes all rows and reinitializes the table

TRUNCATE TABLE TABLENAME --(not reccommended if you 
--don't know the exact use of truncate stmt)

--deleting duplicate rows from a table

Check This[^]

Regards
J O H N

"Even eagles need a push." David McNally

Concurrency Control

leminhphuong3-Sep-07 21:06

leminhphuong

3-Sep-07 21:06

Hi!
I have a project to talk about "Concurrency Control" in SQL Server. Please! give me some imformation (or ebooks)talking about "Concurrency Control".
Thanks!

Re: Concurrency Control

Pete O'Hanlon3-Sep-07 22:04

Pete O'Hanlon

3-Sep-07 22:04

Here[^] and here you go[^]. The first hits in Google[^].

Deja View - the feeling that you've seen this post before.

i want to pass parameter from one report to other

salman kazi3-Sep-07 20:06

salman kazi

3-Sep-07 20:06

i am making reports using sql2005 reporting service. i have 2 reports in a project.what i want is that when i click the value in first report that value is going as a parameter to other report (2nd report) and the second report will generate the result on the basis of the value that what clicked in report 1.
thanks

Salman

Re: i want to pass parameter from one report to other

DLM@TD14-Sep-07 12:06

DLM@TD

14-Sep-07 12:06

Hi,

you should create Linked reports and that should be very easy. Here is a complete tutorial: http://msdn2.microsoft.com/en-us/library/aa337452.aspx

FREE cross database comparison and synchronization tools
DBTYP.NET - see database differences, for free (SQL Server, MySQL, Oracle)

Recursive Trigger

John Gathogo3-Sep-07 19:48

John Gathogo

3-Sep-07 19:48

I create an insert trigger that calls a stored procedure. If an error is encountered when calling the stored procedure, then the trigger appears to affect the insert action such that multiple records are inserted recursively. How can this problem be sorted out.

how to format smalldatetime

prasadbuddhika3-Sep-07 18:52

prasadbuddhika

3-Sep-07 18:52

when use the getdate() function it gives the date and the time ,can't i get only the date without time.

Re: how to format smalldatetime

John Gathogo3-Sep-07 20:18

John Gathogo

3-Sep-07 20:18

You can try the following:

CONVERT(NVARCHAR(20), GETDATE(), 101)

You can play arround with the third parameter to get different date formats

Say, CONVERT(NVARCHAR(20), GETDATE(), 102), etc

what is the shortcut key to open sql management studio in windows

pashitech3-Sep-07 7:19

pashitech

3-Sep-07 7:19

hi

like i use devenv to open visual studio environment
what is the shortcut key for Sql Server

Regards,
Prashanth

Re: what is the shortcut key to open sql management studio in windows

Paul Conrad3-Sep-07 7:53

Paul Conrad

3-Sep-07 7:53

You have to configure a shortcut key to use SQL Management Studio from within VS, I think.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: what is the shortcut key to open sql management studio in windows

Pete O'Hanlon3-Sep-07 8:29

Pete O'Hanlon

3-Sep-07 8:29

Assuming you've installed Sql Server Management Studio into it's default location, you would use "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe", so if you have the path set up you would use SqlWb.

Deja View - the feeling that you've seen this post before.

How to retrieve local machine time using sql query in Sqlserver 2005

pashitech3-Sep-07 7:17

pashitech

3-Sep-07 7:17

Hi all,
when i use select convert(varchar(10),getdate(),101)
query the output is server system date

But i want my local system's date

regards,
Prashanth

Re: How to retrieve local machine time using sql query in Sqlserver 2005

Paul Conrad3-Sep-07 7:54

Paul Conrad

3-Sep-07 7:54

pashitech wrote:
i want my local system's date

If SQL Server is not on your local machine, you cannot do this.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Re: How to retrieve local machine time using sql query in Sqlserver 2005

jschell3-Sep-07 8:00

jschell

3-Sep-07 8:00

SQL on MS SQL Server runs on the server itself. It has no access to your local machine.

That is conceptually how all database systems work.

If you want the local time then send it to SQL server with the rest of the request. However note that that probably isn't a good idea in terms of SQL Server. Timezone problems make that very problematic. And if you are not dealing with timezones then it shouldn't matter whether the client or server time is used.

how to manage array in sql insert trigger

purushotam3-Sep-07 4:30

purushotam

3-Sep-07 4:30

below given a database structure. in table A if any formid insert then table B auto insert formID, each Distinct EmpID

----- row insert manually----------
===== row insert Automatically========

Table A
FormID FormName
1 Department
2 Employee
-----3(FormID)------Location(FormName)---new insert row

Table B
FormID EmpID
1 1
2 1
====3(FormID)===1(EmpID)======>>autoinsert through trigger
1 2
2 2
=====3(FormID)===2(EmpID)======>>autoinsert through trigger
1 3
2 3
=====3(FormID)===3(EmpID)======>>autoinsert through trigger

any one help me through insert trigger on Tble A

Re: how to manage array in sql insert trigger

Pete O'Hanlon3-Sep-07 5:07

Pete O'Hanlon

3-Sep-07 5:07

Could you please explain what you want in a clearer fashion? I must admit that this is a rather confusing description.

Deja View - the feeling that you've seen this post before.

what is wrong with the code

prasadbuddhika3-Sep-07 3:21

prasadbuddhika

3-Sep-07 3:21

scon.ConnectionString = <br />
             "integrated security= true ;" +"initial catalog =my;" + <br />
              "data source =BUDDHIKA\\SQLEXPRESS;";<br />
<br />
<br />
        scon.Open();<br />
<br />
        sc.Connection = scon;<br />
<br />
        sc.CommandText = "create login " + textBox6.Text +<br />
                 " with password ='" + textBox7.Text + "' ";<br />
<br />
       sc.CommandText += "exec sp_addsrvrolemember " <br />
               + textBox6.Text+ ",sysadmin  ";<br />
<br />
      sc.CommandText += "create user " + textBox6.Text + " from login " <br />
               + textBox6.Text + " use my ";<br />
<br />
      sc.CommandText += "execute sp_addrolemember " <br />
           + comboBox3.SelectedItem.ToString() + "," + textBox6.Text <br />
           + " use my";<br />
                <br />
      SqlDataReader sd = sc.ExecuteReader();<br />
<br />
      sd.Close();<br />
<br />
      sc.CommandText = "insert into my.dbo.sys_usr values <br />
     ('" + comboBox4.SelectedItem.ToString() + "','" + textBox1.Text <br />
       + "','" + textBox46.Text + "','" + textBox2.Text + "','"  <br />
      + comboBox2.SelectedItem.ToString() + "','" <br />
       + Box3.SelectedItem.ToString() + "')";<br />
<br />
     sd = sc.ExecuteReader();

when i run this code get an error

Incorrect syntax near the keyword 'with'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Re: what is wrong with the code

Blue_Boy3-Sep-07 4:19

Blue_Boy

3-Sep-07 4:19

prasadbuddhika wrote:
sc.CommandText = "create login " + textBox6.Text +
" with password ='" + textBox7.Text + "' ";

try to replace with

<br />
sc.CommandText = "create login " + textBox6.Text +<br />
" with [password] ='" + textBox7.Text + "' ";

I Love SQL

Re: what is wrong with the code

Paddy Boyd3-Sep-07 4:26

Paddy Boyd

3-Sep-07 4:26

What is wrong with this code is the huge potential for someone to inject SQL into your database. You should look at paramterising this.

Re: what is wrong with the code

Pete O'Hanlon3-Sep-07 5:06

Pete O'Hanlon

3-Sep-07 5:06

Apart from leaving you wide open to SQL Injection attacks, the variable names are really bad. What would you expect if I swapped textBox46 and textBox2 around here? How could you tell when looking at the code?

Deja View - the feeling that you've seen this post before.

Re: what is wrong with the code

Colin Angus Mackay4-Sep-07 0:52

Colin Angus Mackay

4-Sep-07 0:52

The code is practially unreadable! You have left yourself open to SQL Injection Attacks so you might want to find out how to prevent SQL Injection Attacks[^].

Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ...

My website

Xml BulkLoad

Panchal Hardik3-Sep-07 0:54

Panchal Hardik

3-Sep-07 0:54

Hi
I am getting following error on connection string in xmlbulkload:

"QueryInterface for interface SQLXMLBULKLOADLib.ISQLXMLBulkLoad failed."

Thanks

Hardik Panchal

Re: Xml BulkLoad

Panchal Hardik3-Sep-07 2:39

Panchal Hardik

3-Sep-07 2:39

I got the solution.
thanks

Hardik Panchal

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