Re: How to retrieve local machine time using sql query in Sqlserver 2005 - Database Discussion Boards

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

Re: Xml BulkLoad

Pete O'Hanlon3-Sep-07 2:51

Pete O'Hanlon

3-Sep-07 2:51

Now, if you post your solution to your problem, I'll give it a 5 vote.

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

Re: Xml BulkLoad

Panchal Hardik3-Sep-07 2:58

Panchal Hardik

3-Sep-07 2:58

I create the one thread and call my bulk upload method from that thread.
which is work for me. before i not use the thread which is not working in my new application but working in other application.

Here is the code.
Public objBulkthread As System.Threading.Thread
If Not IsNothing(objBulkthread) Then
If Not objBulkthread.IsAlive Then
objBulkthread = New System.Threading.Thread(AddressOf objGeneral.UploadXmlBulk)
objBulkthread.ApartmentState = Threading.ApartmentState.STA
objBulkthread.Start()
End If
Else
objBulkthread = New System.Threading.Thread(AddressOf objGeneral.UploadXmlBulk)
objBulkthread.ApartmentState = Threading.ApartmentState.STA
objBulkthread.Start()
End If

Thanks

Hardik Panchal

Combine result

RichardBlare3-Sep-07 0:39

RichardBlare

3-Sep-07 0:39

Hi, would you please help how to solve it ?
The following is my sql query.

1.
Select RegionCode, count(CountryCode)
From Country
Where RegionCode ='NorthAmerica'
Group by RegionCode

2.
Select RegionCode, count(CountryCode)
From Country
Where RegionCode ='SouthAmerica'
Group by RegionCode

First query return like this

NorthAmerica 5

Second query return like this

SouthAmerica 2

If I use UNION, it combine as rows

NorthAmerica 5
SouthAmerica 2

What I want is

NorthAmerica SouthAmerica
5 2

How can I combine the results ?

Thanks in advance,

RB

Re: Combine result

Pete O'Hanlon3-Sep-07 0:56

Pete O'Hanlon

3-Sep-07 0:56

If you are using SQL Server 2005, take a look at the Pivot command.

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

Re: Combine result

RichardBlare3-Sep-07 23:19

RichardBlare

3-Sep-07 23:19

Thanks for your help Pete.
But, I don't know how to use PIVOT to get my result.
Would you please show me some good samples ?

Thanks,

Help me!

chanzeb2-Sep-07 21:01

chanzeb

2-Sep-07 21:01

Hi everybody,
I want to retrieve the databse schema.database is in sql server2005.The language is c#.please send me some reading related meterial.

Regards,
chanzeb chaudhary.
chanzeb_chaudhary@hotmail.com

Re: Help me!

Pete O'Hanlon2-Sep-07 21:44

Pete O'Hanlon

2-Sep-07 21:44

Here[^] you go. This is a database agnostic way of retrieving information about database schemas.

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

Re: Help me!

chanzeb3-Sep-07 0:15

chanzeb

3-Sep-07 0:15

thanks alot. Smile | :)

.....

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