Incorrect sytax newar COLLATE most of the places - Database Discussion Boards

I would suspect that VB6 is doing a conversion locally. Sql Server does not have a Time only data type. You can always store the time in an integer field if this bothers you. Just convert it to seconds or microseconds since midnight.

I have a select query

select col1-tbl2,col2-tbl3,col3-tbl4 ,col4-tbl1,col5-tbl1 from tbl1where condition,

The above quey col1-tbl2,col2-tbl3,col3-tbl4 are from table1 only, but it have only id(ie) the foreign key references of tables 2,3,4

I want the column with their values to join with the tables 2,3,4.

Except USING CASE
Except USING CASE

Please Guide me

-

The syntax for table and column names in SQL is tablename.columnname
This can be extended to schemaname.tablename.columnname and databasename.schemaname.tablename.columnname and even servername.databasename.schemaname.tablename.columnname

Putting col1-tbl2 looks like you are tying to do a subtraction operation - which is confusing at best.

Your col1, col2, etc. are all referring to either primary keys or foreign keys in which case you should, for clarity, name them to indicate that which reduces an additional burden on those trying to help you. (I hope you don't write code like this in real life)

SELECT tbl2.col1
FROM tbl1
INNER JOIN tbl2 ON tbl1.tbl2FK = tbl2.PK
INNER JOIN tbl3 ON tbl1.tbl3FK = tbl3.PK.....

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

I don't know why you need all those joins - you don't seem to be using them. I'm assuming that somewhere the data that you are using in the case sits in another table.

For example:

SELECT InterimRentReceivableUpfrontTaxModeName
FROM InventoryProfile
INNER JOIN TaxModes 
    ON TaxModes.ID = InventoryProfile.InterimRentReceivableUpfrontTaxModeID

You need one INNER JOIN for each ID you want to turn into a name.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Please don't delete messages - it is impolite when someone has already replied.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

hi
currently i m using following query for counting and grouping

select count(*), col1 from table group by col1 having count(*) > 1

this query returns result like
count(*)-----col1
2----val1
4----val2

now i want to find the number of rows of this returning table so that i can figure out how many col1 values have count > 1

Shajeel

<br />
Select Count(*)<br />
from<br />
(<br />
select col1 <br />
from table <br />
group by col1 <br />
having count(*) > 1<br />
) d<br />

Should do the trick

Wout Louwers

hi I got this procedure to create database and tables using COLLATE from here but i am getting errors incorrect syntax near COLLATE I have checked several times but could not find the actual root cause of it.
if i take any database say master or model it gives me th following errors:

Server: Msg 170, Level 15, State 1, Line 3Line 3: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
Server: Msg 911, Level 16, State 1, Line 2Could not locate entry in sysdatabases for database 'RiversideInternetForums'. No entry found with that name. Make sure that the name is entered correctly.
Server: Msg 170, Level 15, State 1, Line 6Line 6: Incorrect syntax near 'COLLATE'.
Server: Msg 4902, Level 16, State 1, Line 2Cannot alter table 'dbo.WS_Webs' because this table does not exist in database 'model'.
Server: Msg 1906, Level 11, State 1, Line 2Cannot create an index on table 'dbo.WS_Posts', because this table does not exist in database 'model'.
Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'WS_Webs'.

if i create a new database name RiversideInternetForums i get the following errors:

Server: Msg 3702, Level 16, State 3, Line 2Cannot drop the database 'RiversideInternetForums' because it is currently in use.
Server: Msg 170, Level 15, State 1, Line 3Line 3: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
Server: Msg 170, Level 15, State 1, Line 6Line 6: Incorrect syntax near 'COLLATE'.
Server: Msg 4902, Level 16, State 1, Line 2Cannot alter table 'dbo.WS_Users' because this table does not exist in database 'RiversideInternetForums'.Cannot create an index on table 'dbo.WS_Posts', because this table does not exist in database 'RiversideInternetForums'.Cannot create an index on table 'dbo.WS_Users', because this table does not exist in database 'RiversideInternetForums'.
Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'WS_Webs'.

i have wriiten to the author but no response .

can u tell me what is wrong .so can anyone here help me out.

thanks in advance

sasi

Without seeing the code to go along with the error messages it is a bit difficult to help.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Can anyone suggest a good book on MS SQL Server replication? D'Oh! | :doh:

Programming from a spec is like walking on water ......

Its Easier once frozen Wink | ;-)

Hi Exprets,

I am making an application using MS SQL Server and I need to know that what kind of relationships are existing between table e.g. 1-1 or 1-M. I can get the constraint's, coulumn, table, types information from INFORMATION_SCHEMA at this point but I still need to find the relationship types between tables.
I am using C# and ADO.NET and the version of Database Server right now is MS SQL Server 2000 but Finally I will be utilizing MS SQL Server 2005.

Any idea, suggestion, snippet?

Thankx

Hi, I am currently trying to get the SQL query that created a table in SQl server in the first place.I know you can right click on a table name and say Script to Object window as create.. but wanna know how this works? Is there an equivalent for get_ddl function of Oracle in SQL server?

Thanks a lot for the help in advance
Prahalad

I presume it looks up the various sys* tables (e.g. sysobjects & syscolumns) to find out what is in the database and create a script based on that.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

I have a small c# application that connects to a sql server (msde, 2000 or 2005). At a certain point I scheduleld a database backup to a file like this :

BACKUP DATABASE myDatabase TO DISK = 'c:\test folder'

Nothing wrong if the server is on the computer that runs tha small application, but if the server is on a remote computer the file is saved on that one instead on the one that runs the application.
How can I save the file on the computer that acceses the sql server (na- how can I make the server interact upon backup-restore with an other computer over the network)?

protected internal static readonly ... and I wish the list could continue ...

Vlad Stanciu wrote:
Nothing wrong if the server is on the computer that runs tha small application, but if the server is on a remote computer the file is saved on that one instead on the one that runs the application.

That is correct because the command is running on the remote server so all disk access will be as seen from the SQL Server, not your local machine.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

So how can I connect to a remote server and backup a database localy?

protected internal static readonly ... and I wish the list could continue ...

You don't. You backup on the remote machine then copy the file across the network.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Thanks for your answer!

Now I have another question, kind of offtopic... why didn't MS implemented the functionality to backup batabases to the terminals (remote pc's).
Security? I mean if you are able to backup a database you are able to querry it too, so there is noting to protect...

protected internal static readonly ... and I wish the list could continue ...

Vlad Stanciu wrote:
Security? I mean if you are able to backup a database you are able to querry it too

Not necessarily. You can assign a user account the the db_backupoperator role which permits only backing up the database (assuming no other permissions are granted).

Storing the backups on the local machine only means that you have to provide additional security rights in order to get the backup off the machine in order to restore it elsewhere.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

One of our projects is using Enterprise Library for data access (i.e. Data Access block). I looked at the code and don't see what's the advantage of using Data Access Block...

But one big advantage I is, you don't need to "Close" connection.

Personally, I find NHibernate much more helpful in minimizing data access chores...

Norman Fung

Right Now I have this code:

Private Sub add()
Dim firstvalue As String = txtName.Text
Dim secondvalue As String = txtSurname.Text

Dim conn As OleDbConnection = New OleDbConnection(strConnection)
Dim cmd As OleDbCommand = New OleDbCommand
cmd.Connection = conn
cmd.CommandText = "INSERT Into tblMembers(Name, Surname) VALUES (?,?)"
cmd.Parameters.Add("Name", firstvalue)
cmd.Parameters.Add("Surname", secondvalue)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Sub

But an error is occuring telling me this:

Exception Details: System.Data.OleDb.OleDbException: Operation must use an updateable query.

What do you thing this is?? Help me please!!

Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.

hi i want to make an select statment that contains some data, that is
SELECT Name,ID, Sname,Schools from student

imagine that this table have one student have the same ID and same name but i need the last one how can do that while there are no Primary keys .
can i use grouped by Name alone or i should make a grouped by name,ID,..

Amarni

Amarni wrote:
imagine that this table have one student have the same ID and same name but ... there are no Primary keys

So what is ID if not the primary key?

Amarni wrote:
i need the last one

What do you mean by "the last one"? Databases contain sets of data there is no implied order of anything in the database. If you want to make something ordered then it has to be ordered on a column or columns. Which column defines the order?

Amarni wrote:
can i use grouped by Name alone or i should make a grouped by name,ID,..

GROUP BY will allow you to aggregate the other columns based on the column or columns you are GROUPing BY. That means you will no longer be able to access ID, Sname, Schools other than through an aggregation function such as MIN(), MAX(), SUM(), AVG() etc.

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

I use a table named FDetails with 4 text fields and a datetime field named date in MSaccess
when insert values into table ising ado recordset obj I receive some syntax error
recordset->Open("INSERT INTO FDetails(name,path,duration,volume,date) VALUES('hello','location','duration','vol','12/12/2000')",connection.GetInterfacePtr(), ADODB::adOpenForwardOnly,ADODB::adLockReadOnly, ADODB::adCmdText);

any one know inserting date into table

I have a question SQL Server cursor question

I have a table, “clients”. I want to create a cursor to select and iterate through each client in the database. Obviously, that will lock the “clients” table. Fine. But if I involve another table to do a filter (in the where clause, to reduce the result set) does that lock both tables and just the “Client” table?

Thanks in advance,
Frank
www.TheOpenSourceU.com