new to SQL - create table command question

hi

can someone please explain the following please

why does this fail with specified owner testdb does not exist or you do not have permissions

create testdb.testtable (

testval int

)

yet if i create testdb and then run the following against this new db

create testtable (

testval int

)

command command completes successfully......what am i not understanding or doing wrong?

thank you

oooops....sorry

im so new i cant even type the command correctly

this command fails....

create table testdb.testtable (

testval int

)

this command works....

create table testtable (

testval int

)

|||Depending on the version of SQL Server you are using, the prefix testdb indicates either the owner of the table or the schema name that the object / table will be stored in. it does not identify the database where the object has to be created in. For the database identifier you will have to you the three partname which would be (assuming that you want to store the object in the users default schema (for SQL 2k5) or within his owner "schema":

CREATE TABLE testdb..SomeTable
(SomeColumn int)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

New to CR

I'm coming in on the back end of a project and I don't understand the following record selection formula. Would someone please explain what it's doing?

if {?@.rptfilter} = 0 then
(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 1 then
({CAS_SP_SolnLosses;1.Contractor} = "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} = "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 2 then
({CAS_SP_SolnLosses;1.Contractor} <> "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} <> "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})This is not an answer to your question, but at least in earlier versions (up to version 8.5) Crystal reports is not able to create an SQL stement based on control structures (If-Then-Else) instead you should convert the record selection formula to a boolean statement using ands and ors and braces. What happens if you don't do this is that Crystal loads the entire answer set to your computer and makes the record selection locally. This has at least two major disadvantages:
1. The query is processed on the database server as a full table join and the query can not make use of any indexes. This can cause performance problems to any DBMS system
2. You cause a lot of network traffic moving unneeded data over the network

This has one implication, which is poor performance.

What concerns your code I have a few suggestions:

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
could be removed in my opinion, because it compares a field to both null and not null

after this you basically create different joins based on different values of
{?@.rptfilter}

Hope this gives you some clues.

- Jukka|||This is not an answer to your question, but at least in earlier versions (up to version 8.5) Crystal reports is not able to create an SQL stement based on control structures (If-Then-Else) instead you should convert the record selection formula to a boolean statement using ands and ors and braces. What happens if you don't do this is that Crystal loads the entire answer set to your computer and makes the record selection locally. This has at least two major disadvantages:
1. The query is processed on the database server as a full table join and the query can not make use of any indexes. This can cause performance problems to any DBMS system
2. You cause a lot of network traffic moving unneeded data over the network

This has one implication, which is poor performance.

What concerns your code I have a few suggestions:

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
could be removed in my opinion, because it compares a field to both null and not null

after this you basically create different joins based on different values of
{?@.rptfilter}

Hope this gives you some clues.

- Jukka

I agree that line you mentioned could be deleted. My problem is understanding the syntax of this *ugly* formula. Unfortunately, the developer that wrote this is long gone and, like I mentioned earlier, it's been dumped in my lap. I've looked over the .pdf's on the CD hoping to find some help, no luck. Are there any other sources which would help me get my head around understanding what this code is doing?

TIA

BTW, is there a way to step thru the formula code with a debugger?|||Hello,

I'm doing project using Access as backend and VB6 FrontEnd. I have the following problem during compilation. I'm using Runtime Activex lib of CR 8.5.

Error: Method or Data member not found

===> Private Sub mnu_stock_Click()
With Form1.CrystalReport1
.DataFiles(0) = App.Path & "\MyDB.MDB"
.ReportFileName = App.Path & "\Report\RPT_AVA_STOCK.rpt"
.username = "Admin"
.Password = "1010101010" & Chr(10) & "1010101010"
.Action = 1
.PageZoom (100)
End With
End Sub

Thank you,

Regards,

Niranjan Dixit|||I agree that line you mentioned could be deleted. My problem is understanding the syntax of this *ugly* formula. Unfortunately, the developer that wrote this is long gone and, like I mentioned earlier, it's been dumped in my lap. I've looked over the .pdf's on the CD hoping to find some help, no luck. Are there any other sources which would help me get my head around understanding what this code is doing?

TIA

BTW, is there a way to step thru the formula code with a debugger?

Here is a try (the syntax is slightly different to what I'm used to using Oracle):
You have the following parameters in your report controlling the outcome:
rptfilter
typeid

comments are marked with a "-"

Fuurther I would still make sure that I have all parenthesis in the right places. I would include an extra round of parenthesis where rptfilter changes values

and the code it self:
if {?@.rptfilter} = 0 then
- rpt filter = 0

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
- This section does nothing

and
(if {?@.typeid} = 0 then
- rptfilter = 0 and typeid = 0

ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
- When rptfilter = 0 and typeid = 0 then only record having CAS_SP_SolnLosses;1.Typevalue = "ANY" are included

else
if {?@.typeid} = 1 then
- rptfilter = 0 and typeid = 1

{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
- When rptfilter = 0 and typeid = 1 then is joined to CAS_SP_SolnLosses;1.Typevalue = {CAS_SP_SolnLosses;1.Region}, i.e. values have to be equal
- Similar for typeid 2 - 8

else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else

if {?@.rptfilter} = 1 then
- rptfilter = 1

({CAS_SP_SolnLosses;1.Contractor} = "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} = "CAJV")
- rptfilter = 1 and CAS_SP_SolnLosses;1.Contractor = "ARORA" or CAS_SP_SolnLosses;1.Contractor "CAJV"

and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
- This is similar to above except that rptfilter has value 1 and further below value 2

else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 2 then
({CAS_SP_SolnLosses;1.Contractor} <> "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} <> "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})|||Here is a try (the syntax is slightly different to what I'm used to using Oracle):
You have the following parameters in your report controlling the outcome:
rptfilter
typeid

comments are marked with a "-"

Fuurther I would still make sure that I have all parenthesis in the right places. I would include an extra round of parenthesis where rptfilter changes values
<snip>

Thanks for your help!

New Query with Current Connection

I am missing "New Query with Current Connection" button in SQL Editor in the
SQL management studio. Few posts on the Web refer explain that this button
should allow me to open a new query in the existing window.
I've checked "Add Remove Buttons" for SQL editor but I still cannot find it.
Any ideas?
Bojan Kuhar wrote:
> I am missing "New Query with Current Connection" button in SQL Editor in the
> SQL management studio. Few posts on the Web refer explain that this button
> should allow me to open a new query in the existing window.
> I've checked "Add Remove Buttons" for SQL editor but I still cannot find it.
> Any ideas?
I'm not sure it can be done, but "Ctrl-N" will give you the "New query
with current connection".
Regards
Steen
|||Fantastic.
Now, one more. Is there something like "Open Query with Current Connection".
I'd like to avoid the Connect to Database Enginde dialog.
More-less like Qury Analyzer that allows you to open a new query in the
existing window.
Regards
Bojan
"Steen Persson (DK)" wrote:

> Bojan Kuhar wrote:
> I'm not sure it can be done, but "Ctrl-N" will give you the "New query
> with current connection".
> Regards
> Steen
>
|||My fault. The button was called New Query with Current Connection in one of
the betas. The released version renamed it to New Query. I changed most of
the references, but I missed a few. I think I got rid of them all by web
release 1 of Books Online though.
The New Query button will use the connection that has focus when you click
it. Either Object Explorer, or an existing Query Editor window. If your
current context has no connection, then it will pop up the Connect to Server
dialog box.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Bojan Kuhar" <BojanKuhar@.discussions.microsoft.com> wrote in message
news:01230DB5-BEE5-4452-B89E-470DCCE488F2@.microsoft.com...
>I am missing "New Query with Current Connection" button in SQL Editor in
>the
> SQL management studio. Few posts on the Web refer explain that this
> button
> should allow me to open a new query in the existing window.
> I've checked "Add Remove Buttons" for SQL editor but I still cannot find
> it.
> Any ideas?
|||Bojan Kuhar (BojanKuhar@.discussions.microsoft.com) writes:
> Fantastic.
> Now, one more. Is there something like "Open Query with Current
> Connection". I'd like to avoid the Connect to Database Enginde dialog.
> More-less like Qury Analyzer that allows you to open a new query in the
> existing window.
What Steen said, CTRL-N is the key. Provided one thing: under Tools->
Options->Keyboard select SQL 2000 instead of Standard.
The New Query button also works if you are in a query window or Object
Explorer, and so does File->New->Query with Current Connection.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx