Database

Re: The highest value for VarBinary(MAX) in Production Server

10-Aug-07 2:16

Could it be possible that the remote SQL server is not version 2005? As I recall, SQL 2000 doesn't have the MAX feature.

-- If this is a post that has been helpful to you, please vote for it. Thank you!

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

Luis Alonso Ramos12-Aug-07 14:49

Luis Alonso Ramos

12-Aug-07 14:49

Or also, that your MyLittleAdmin software doesn't support the MAX size. If you have SQL 2005 on your production server, MAX is supported.

Luis Alonso Ramos
Intelectix
Chihuahua, Mexico

My Blog!

how can i write a query....

koolprasad200310-Aug-07 0:06

koolprasad2003

10-Aug-07 0:06

hello frnds,

i want a query which display a record in following manner

suppose i give

select * from TestTable where testName like 'V%'

the result shows the all names start from characters "V"
but is there any way to accept a numberonly after "V"

hope u can help...
thankx in advance
-koolprasad2003 Smile | :)

If the message is useful for U then please Rate This message...

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

Re: how can i write a query....

andyharman10-Aug-07 2:19

andyharman

10-Aug-07 2:19

If you are using SQL-Server then:
... where testName like 'V[0-9]%'
If you are not using SQL-Server then:
... where left(testName,1,2) between 'V0' and 'V9'
Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

pivot query in sybase

pete007_ke9-Aug-07 22:08

pete007_ke

9-Aug-07 22:08

greetings people,

simply wondering whether there is another of doing this is standard ANSI Transact SQL.
So is here is the query that im running in SQL Server 2005:

Select flavour, isnull([Period 1],0) as [Period 1] , isnull([Period 2], 0) as [Period 2] from (
select sum(crates) as crates, flavour, dbo.getperiod(saledate,'08 Jul 2007','08 Aug 2007') as Period
from ReportSummary where
dbo.getperiod(saledate,'08 Jul 2007','08 Aug 2007') <> 'unknown' and producttypeid=10
group by flavour,dbo.getperiod(saledate,'08 Jul 2007','08 Aug 2007')

) as p
pivot
(
sum(crates) for period in ([Period 1],[Period 2])
) as pvt

and the result set is as follows:

FLAVOUR Period1 Period2
Bitter Lemon 894.41 0.00
Blackcurrant 892.53 0.00
Citrus 481.25 2.00
Coca-Cola 1346.00 0.00
Coke Light 131.75 0.00
Ginger Ale 404.88 0.00
Orange 2667.92 0.00
Soda Water 327.80 0.00
Sprite 1886.49 0.00
Stoney Ginger Beer 559.71 0.00
Tonic Water 250.00 0.00

my problem is that i need to run the same query in a Sybase databse.HOwever, Sybase does not have the pivot operator. Any other of doing the same thing??

Kind regards,
Pete.

Re: pivot query in sybase

andyharman10-Aug-07 2:24

andyharman

10-Aug-07 2:24

Hi Pete

You should be able to adapt the following technique:

select flavour,
    sum(case when period = 1 then crates else 0 end) as Period1_Total,
    sum(case when period = 2 then crates else 0 end) as Period2_Total
  from ReportSummary
  group by flavour
  order by flavour

The case clause counts just the crates for the stated period.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Error in connecting remotely to database

IamAmit9-Aug-07 3:18

9-Aug-07 3:18

hello friends..I got this error while conecting to sqlsever 2005 through Visual studio 2005
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
can anyone help me..to resolve it

Talal Sultan9-Aug-07 3:55

9-Aug-07 3:55

I have just suggested here http://www.codeproject.com/script/comments/forums.asp?msg=2175289&forumid=1725#xx2175289xx[^] to check if the machine where the SQL Server is located has any firewall active.

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."
--Rich Cook

originSH9-Aug-07 5:10

originSH

9-Aug-07 5:10

Well read the error message, notice it says

Amit.Jagtap.nsk wrote:
this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections

Do a google search and you'll get plenty of hits

http://www.google.co.uk/search?hl=en&q=sql+server+express+enable+remote+connections&meta=[^]

pick a likly looking one and work through what it says

http://www.datamasker.com/SSE2005_NetworkCfg.htm[^]

If your not using Express then say what edition you are using. If you have already enabled remote connections then say so.

IamAmit9-Aug-07 23:09

9-Aug-07 23:09

Thank you,

I tried all these suggestions but still connection is not established..Please Help me

Talal Sultan9-Aug-07 23:59

9-Aug-07 23:59

huh...could it be that your server URL is wrong by any remote chance? Smile | :)

IamAmit10-Aug-07 0:04

10-Aug-07 0:04

I write connection string as
string str="server=pcname/SQLEXPRESS; Initial Catalog=databasename;user=username;password=password ";
Is it right. I am using sql server 2005 express edition

Talal Sultan10-Aug-07 0:11

10-Aug-07 0:11

Could you try:

"data source=PCName\ServerName;initial catalog=DBName;User ID=Username; Password=Password;"

and see what happens?

IamAmit10-Aug-07 0:22

10-Aug-07 0:22

I tried it ..But the result is the same...I also allow remote connections through TCP/IP in sql server configuration manager..

sam#10-Aug-07 1:35

sam#

10-Aug-07 1:35

you are using wrong connection string
Use this..

Data Source=REMOTE_SQL_SERVER;Network Library=DBMSSOCN;Initial Catalog=MY_DATABASE;User ID=MY_DB_USER;Password=MY_USER_PASSWORD;

Talal Sultan10-Aug-07 1:56

Server Error i need to solve this error

10-Aug-07 1:56

Well the connection string I gave works perfectly for me. However, it would be interesting to try the SSPI thing:

"data source=PCName\DBServerName;initial catalog=DBName;Integrated Security=SSPI"

Bulk insert

John.L.Ponratnam9-Aug-07 3:03

John.L.Ponratnam

9-Aug-07 3:03

I have done bulk insert. When I do the bulk insert next time; new item has to be inserted and if the item exists already it must be updated. Can this be possible? If its possible plz explain it with coding

Regards,
LEE

Re: Bulk insert

andyharman9-Aug-07 4:41

andyharman

9-Aug-07 4:41

Hi John

In this case, I normally do a bulk insert into a temporary table then run the following to update existing records:

update MMT set
    Value1 = T1.Value1,
    Value2 = T1.Value2,
    LastUpdateDate = GetDate()
  from MyMainTable MMT
  inner join #MyBulkInsertTable T1
  on T1.Id = MyMainTable.Id
  where T1.Value1 <> MMT.Value1 or T1.Value2 <> MMT.Value2

The where-clause ensures that you only update records that have changed. If the Value1 and Value2 columns can be null then I normally use the IsNull function to force a value in the comparison.

And the following to insert new records:

insert into MyMainTable
  select T1.Id, T1.Value1, T1.Value2, GetDate() as InsertDate,
      GetDate() as LastUpdateDate
  from #MyBulkInsertTable T1
  left outer join MyMainTable MMT
    on MMT.Id = T1.Id
  where MMT.Id is null

This uses an outer-join to identify rows that have not yet been loaded. The "InsertDate" and "LastUpdateDate" columns provide a primative audit trail that can be useful when things go wrong.

A similar technique can be used for deleting records from MyMainTable.

Hope that helps.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Rami Said Abd Alhalim9-Aug-07 2:35

Rami Said Abd Alhalim

9-Aug-07 2:35

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

i need to solve this error

I set TCP\IP protcol server and named pipes in network configration
but this error have still

Rami said Abdalhalim

Blue_Boy9-Aug-07 2:46

Blue_Boy

9-Aug-07 2:46

start service named W3SVC (World Wide Web Publishing)

I Love SQL

Rami Said Abd Alhalim9-Aug-07 2:50

Rami Said Abd Alhalim

9-Aug-07 2:50

this service started before this error

Rami SAid Abd Alhalim

Talal Sultan9-Aug-07 3:51

9-Aug-07 3:51

Is the SQL Server 2005 you are trying to access on another machine? Because if it is, you might be having a firewall on that machine which is preventing your code from access the database server.

Talal

Server: Msg 128, Level 15, State 1, Line 3

Rami Said Abd Alhalim10-Aug-07 19:38

Rami Said Abd Alhalim

10-Aug-07 19:38

thanks talal : but i am using sql server 2000 and i trying test on application without firewall(Stop Firewall),and i opened all ports to connection on server (TCP/IP)port but i have the same problem

IamAmit9-Aug-07 2:00