Database Discussion Boards

Re: SELECT column FROM table WHERE time = @time ISSUE [modified]

Blue_Boy22-Apr-08 0:01

22-Apr-08 0:01

108 is returned format of datetime, also we have another formats like 100,101,102 and so on

If you wanna check this syntax in T-SQL by changing format number

select convert(varchar,getdate(),108)

I Love T-SQL

modified on Tuesday, April 22, 2008 6:09 AM

Re: SELECT column FROM table WHERE time = @time ISSUE

Colin Angus Mackay22-Apr-08 23:46

Colin Angus Mackay

22-Apr-08 23:46

Blue_Boy wrote:
108 is returned format of datetime, also we have another formats like 100,101,102 and so on

Which means what? What does format "101" actually mean? Without further explanation your statement is meaningless. Technically correct, but meaningless all the same.

Upcoming FREE developer events:
* Developer Day Scotland

Recent blog posts:
* Introduction to LINQ to XML (Part 1) - (Part 2)

My website | Blog

Re: SELECT column FROM table WHERE time = @time ISSUE

Blue_Boy23-Apr-08 6:28

Blue_Boy

23-Apr-08 6:28

for more-meaning use google search engine Wink | ;)

I Love T-SQL

Re: SELECT column FROM table WHERE time = @time ISSUE

sonia.sardana23-Apr-08 23:43

sonia.sardana

23-Apr-08 23:43

hey frnd can u tell me what is 108 in the foll Query
select convert(varchar(10),columnname,108) from myTable

Re: SELECT column FROM table WHERE time = @time ISSUE

Sivu21-Apr-08 23:53

Sivu

21-Apr-08 23:53

Hey man,

You can use DATEPART to get the different parts of the date.

<br />
select right('0' + convert(varchar, datepart(hh, time)), 2) + ':' + <br />
       right('0' + convert(varchar, datepart(mi, time)), 2) + ':' + <br />
       right('0' + convert(varchar, datepart(ss, time)), 2)<br />

With the date you've given as an example, this would return the string "23:00:00".

Hope this helps...

Problem with timeout in accessing Excel file

Rocky#21-Apr-08 23:06

Rocky#

21-Apr-08 23:06

hi everyone,
I'm working on importing records from an excel file, the problem is that I'm trying to specify a time out of about 2 minutes but it still expires on 1 minute.

Here's the string that I'm using in my app.config file.

Can anyone plz help me in this regard

Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=D:\Projects\BulkCopy\BulkCopy\BulkCopy\bin\Debug\test1.xls;connect timeout=120;

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Re: Problem with timeout in accessing Excel file

Blue_Boy22-Apr-08 0:20

Blue_Boy

22-Apr-08 0:20

connect timeout=120*2 will give you 2 minutes.
Try it

I Love T-SQL

Re: Problem with timeout in accessing Excel file

Rocky#22-Apr-08 0:28

Rocky#

22-Apr-08 0:28

Actually this time out was comming form the server where I was trying to bulk copy those records so right now I'm giving the time out in SQLBulkCopy object, a few moments ago it ended in failure with 500 as its value. now I'm raising it up to 3600Secs = 1hr

Hope that turns out ok

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Re: Problem with timeout in accessing Excel file

Michael Potter22-Apr-08 4:12

Michael Potter

22-Apr-08 4:12

Just so you know - The Connection Timeout only comes into play when acquiring a connection to the source. It has no bearing on processing which is defaulted to 30 seconds in ADO.Net. I guess the default is 1 minute in SQLBulkCopy. If you ever want to change the ADO.Net processing timeout, it is in the Command class under CommandTimeout.

Re: Problem with timeout in accessing Excel file

Rocky#22-Apr-08 4:17

Rocky#

22-Apr-08 4:17

yes I used Timeout property of SQLBulkCopy and it worked for me.

Thanks for ur reply Smile | :)

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

SqlDumpExceptionHandler Error

Vimalsoft(Pty) Ltd21-Apr-08 22:18

Vimalsoft(Pty) Ltd

21-Apr-08 22:18

Good Morning All

i have the Following Query

---------------------------------------------------------
--Updating the Actual_Extent  with a Floor Area
update  Tshwane_Valuations_Train.sde.Property_Summary_Backup
set Actual_Extent = ip.Actual_Extent
from  Tshwane_Valuations_Train.sde.Property_Summary_Backup p 
inner Join  Returned_file_GEOSS ip on 
p.Property_ID  = ip.Property_ID
where ip.Property_ID = p.Property_ID
And Propery_ID in  
(
select Property_ID from Property_Summary
where Actual_Extent is null
And Func_key like 'GEOSS%'
And Archive_Date is Null
union all
select Property_ID from Property_Summary
where Actual_Extent in (0,1,2,3,4,5,6,7,8,9)
And Func_key like 'GEOSS%'
And Archive_Date is null
 )

This when i tryt to run the query , i get the Following Error

Msg 0, Level 19, State 0, Line 3
SqlDumpExceptionHandler: Process 149 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

What is Wrong,

Thanks

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: SqlDumpExceptionHandler Error

SimulationofSai21-Apr-08 22:57

SimulationofSai

21-Apr-08 22:57

Looks like a bug in SQL Server. Apply the latest SP's and/or Service packs. If you still keep getting this, you may want to contact microsoft support.

SG
Cause is effect concealed. Effect is cause revealed.

Re: SqlDumpExceptionHandler Error

Vimalsoft(Pty) Ltd21-Apr-08 23:10

Vimalsoft(Pty) Ltd

21-Apr-08 23:10

thanks

i have overcomed this by creating a Temp table.It is a Bug in SQL Server, i will take your advice.

Thanks

SQL server mdf and ldf

new2pgrmg21-Apr-08 19:19

new2pgrmg

21-Apr-08 19:19

H,
I have a stored procedure in SQL server 2005 in a database.Right now i am unable to access my SQL Server and it needs reinstallation.But i need the stored procedures i have written previously in this.I know the name of teh database that contains the SP.I can see it in my programmeFiles as mdf and ldf.

Once the reinstallation of my new sql server 2005 is done,i want my new sql server to have this SP.I tried to open my mdf and ldf but its not opening.I tried to copy to some location but it says "another program is using this file"-This is not true.

So my question is how can i do this.I desperatly need ny SQL server after reinstallation to have these SP's since writting them again will be tedious.

Many Thanks

Re: SQL server mdf and ldf

Sivu21-Apr-08 21:32

Sivu

21-Apr-08 21:32

From the message - "another program is using this file" - it's clear that your DB is still attached to the SQL Server. To move it you need to detach it. But for that you need to connect to the server.

When you connect to your 'new' installation, can't you see the 'old' database in object explorer? You should actually have access to the complete database and not need to recreate anything...

Re: SQL server mdf and ldf

SimulationofSai21-Apr-08 21:34

SimulationofSai

21-Apr-08 21:34

What do you mean when you say you're unable to access SQL Server? Lost your password?, or is it some other error you're getting. Are you the owner of the montioned database?

You have two options here

1) Try solving your existing problem and backup the database.
2) Stop the SQL Server Service, copy the mdf and ldf to a different location and do your reinstall. Once you have the new instance up, you can try attaching the mdf, although I seriously doubt if it'll succeed. It's a gamble you've to take.

location but it says "another program is using this file"-This is not true

This actually might mean that your SQL Server instance is still running. Try figuring out what's wrong with your login or post the error you get here.

SG
Cause is effect concealed. Effect is cause revealed.

Re: SQL server mdf and ldf

new2pgrmg21-Apr-08 22:12

new2pgrmg

21-Apr-08 22:12

2) Stop the SQL Server Service, copy the mdf and ldf to a different location and do your reinstall. Once you have the new instance up, you can try attaching the mdf, although I seriously doubt if it'll succeed. It's a gamble you've to take.

I did this and am going to reinstall my SQL server 2005.I hope while attaching it will work.Any way at this point i think we have to take that risk since we dont have any other options.

I will try this and will post the effects.Please be in this loop.

Many Thanks

Column lenth as read from syscolumns table

WBurgMo21-Apr-08 15:44

WBurgMo

21-Apr-08 15:44

I am trying to come up with a query to determine the structure of a table. So far I have come up with the following query:

SELECT SC.name, SC.length FROM syscolumns SC
inner join sysobjects SO on SO.id = SC.id
where SO.name = 'Customers'

The problem is that the "length" column is twice the size it should be.

Is there a flaw in my query or do I just have to divide the "length"
by two?

Thanks

James Johnson

Re: Column lenth as read from syscolumns table

Ashfield21-Apr-08 20:02

Ashfield

21-Apr-08 20:02

Assuming you are using sql server 2000 or later try this:

select * from information_schema.columns<br />
where table_name = 'Customers'

It gives you all the information about the table structure.

Hope this helps

Bob
Ashfield Consultants Ltd

Re: Column lenth as read from syscolumns table

WBurgMo22-Apr-08 9:33

WBurgMo

22-Apr-08 9:33

What I ended up doing is creating a command to execute the following

Select top 1 * from Customers

Then
DataReader dr = cmd.ExecuteReader();
DataTable dt = dr.GetSchemaTable();

The resulting DataTable contains all of the information I need.

Thanks

James Johnson

Re: Column lenth as read from syscolumns table

Ashfield22-Apr-08 21:15

Ashfield

22-Apr-08 21:15

Thats one way Smile | :)

Even better would be

Select * from Customers where 1 = 2

Which will give you an empty table and use less resource on the server

Bob
Ashfield Consultants Ltd

SQL HELP

n1coltsfan21-Apr-08 5:38

n1coltsfan

21-Apr-08 5:38

This data is in SQL 2000 and the Field Name is raw_payload I want to write a query that seperates this field by the '+' as a delimiter into 4 other fields a1 a2 a3 a4 or whatever in the same database. Any help would be awsome.

raw_payload
S9000+
UV+100.0000+0.0000
UV+101.0000+101.0000
UV+102.0000+64.0000
UV+103.0000+0.0000
UV+104.0000+
UV+105.0000+0.0000
UV+106.0000+3.0000
UV+107.0000+0.0000
UV+108.0000+0.0000
UV+109.0000+0.0000
UV+110.0000+0.0000
UV+111.0000+0.0000
UV+112.0000+1494.0000
UV+113.0000+38.6623
UV+114.0000+66.0000
UV+115.0000+8.1340
UV+116.0000+0.0000
UV+117.0000+0.0000
UV+118.0000+0.0000
UV+119.0000+0.0000
UV+120.0000+0.0000
UV+121.0000+0.0000
UV+122.0000+0.0000
UV+123.0000+0.0000
UV+124.0000+0.0000
UV+125.0000+0.0000
UV+126.0000+0.0000
UV+127.0000+0.0000
UV+128.0000+0.0000
UV+129.0000+0.0000
UV+130.0000+0.0000
UV+131.0000+0.0000
UV+132.0000+0.0000
UV+133.0000+0.0000
UV+134.0000+0.0000
UV+135.0000+0.0000
UV+136.0000+0.0000
UV+137.0000+0.0000
UV+138.0000+0.0000

Re: SQL HELP

Michael Potter21-Apr-08 8:21

Michael Potter

21-Apr-08 8:21

This is best handled outside of TSQL since arrays are not a native element in SQL. Therefore, nothing to split the string into.

You can cludge a query using CHARINDEX and SUBSTRING. You will need to add some CASE statements to deal with any missformed data.

Re: SQL HELP

n1coltsfan21-Apr-08 8:36

n1coltsfan

21-Apr-08 8:36

Right now I am exporting from SQL 2000, importing this into Access 2003 exporting out to Excel 2003 parsing the data with macros and re importing to Access to run my reports wanted to reduce all the importing and exporting if possible to speed up the process....

Re: SQL HELP

Michael Potter21-Apr-08 8:56

Michael Potter

21-Apr-08 8:56

It can be done - it will be quicker. The code will be look clumsy.

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