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 SQL

I have a table with a field that is 14 characters long. I have created two more columns with in the same table. I wish to parse each record taking the 14 character field and storing the first 10 characters in one of the newly created fields and take the next 4 characters and store them in the other newly created fields.

Can someone tell me how to do that?Look in BOL (Books Online) at the Left and Right functions.|||I can do the left and right functions. I just don't understand the looping through the records and updating the other fields.|||Run an update query:

UPDATE TableName
SET SecondField = Left(FirstField,10),
ThirdField = Right(FirstField,4)|||Thanks. I was thinking this was going to be more complicated.|||Of course the real question is should you have the other fields in the table, since their values can always be calculated from the first. Generally speaking the answer would be no.

New to SQL

I'm learning SQL and am trying to solve this problem. I have a table of Teams with a TeamID and another table of people with a TeamID col. I am trying to count the number of people on a team and display the team with the most people.

I have used this to choose the number of people on the teams:

SELECT tname, COUNT(tid)
FROM teams t, people p
WHERE t.tid=p.tid
GROUP BY tname;

this displays the team name and the # of members

How can I choose the team with the most members?

Could I do something such as (the syntax may not be correct, but is the overall idea?):

SELECT tname, count(*)
FROM tname
GROUP BY tname
HAVING count(*) >= all
(SELECT tname, COUNT(tid)
FROM teams t, people p
WHERE t.tid=p.tid)Try:
SELECT t.tname, count(*)
FROM teams t, people p
GROUP BY t.tname
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
)|||no that printed out the team names and 23 for each team. 23 is the number of rows in people|||That's because I forgot the join:

SELECT t.tname, count(*)
FROM teams t, people p
WHERE p.tid = t.tid
GROUP BY t.tname
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
)|||thanks that worked... Maybe someone could explain how to think through a query in order to solve it...I have programmed C++ which is a procedural, and SQL is not. I'm having trouble relating a question given in english to how it should be written in SQL|||Yes, it does require a different frame of mind. I'll try to explain how I got to it...

The requirement is "display the team with the most people". To find out the number of people in each team we need to look at the people table:

SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid;

But from those results we only want that group having (hint) the highest count:

SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid
HAVING count(*) >= ALL (<counts by p.tid>;

Now this query serves for <counts by p.tid>:

SELECT COUNT(*)
FROM people p
GROUP BY p.tid;

It will return a list of counts like:
11
7
13
2

So we now have:

SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
);

The final part is more or less cosmetic: show the team name instead of the tid. We do that by joining to the team table in the main query, and then grouping by the team name instead of the tid (since, I assumed, both are unique within the teams table). That gives us the final query:
SELECT t.tname, count(*)
FROM teams t, people p
WHERE p.tid = t.tid
GROUP BY t.tname
HAVING count(*) >= ALL
( SELECT COUNT(*)
FROM people p
GROUP BY p.tid
);

There is more than one way to do it, and it is really a matter of experience and practice to become proficient at solving such problems.|||Thank you. That was explained well.|||Suppose I wanted to see which teams had 4 or more. I tried to change all to 4, which worked, but when I tried to output the names of people, nothing was displayed. I assume it because this column is removed some where in the having clause, but how do I retain that information...once again, thanks for taking the time to write the last reply!|||Hello,

Well you should have had :

SELECT t.tname, count(*)
FROM teams t, people p
WHERE p.tid = t.tid
GROUP BY t.tname
HAVING count(*) >=4;

So you display team names and their number of players for teams having at least 4 players.

Now, I don't understand why you speak of people name. You won't have them with this query.

What do you exactly want ?

Regards,

RBARAER|||Following on from RBARAER's query, you can get all the people's names like this:

SELECT t.tname, p.pname
FROM teams t, people p
WHERE p.tid = t.tid
AND p.tid IN
( SELECT p.tid, count(*)
FROM people p
GROUP BY p.tid
HAVING count(*) >=4
)
ORDER BY t.tname, p.pname;|||I'm sorry, when I said people name, it is actually fname and lname. when i tried the code it gave me an error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

why does it not let me enter p.lname and p.fname in the outer select?|||NO WAIT...it wasnt the outer select, it was the inner...it does not allow p.tid in the inner select statement. I removed it and it did not produce the correct answer. It returned a group name with 4 members and a gname with 1 member|||Then:
SELECT t.tname, p.fname, p.lname
FROM teams t, people p
WHERE p.tid = t.tid
AND p.tid IN
( SELECT p.tid
FROM people p
GROUP BY p.tid
HAVING count(*) >=4
)
ORDER BY t.tname, p.pname;
If that doesn't work, please post the exact SQL and error code/message.

(I have removed the COUNT(*) that I accidently left in before.)|||perfect!! How does it count the number of members without actually using count?|||oops...sorry, didnt notice it in the having

New Time Dimension Role after cube wizard

If I have a data source view consisting of two tables - a fact table and a time dimension table. The fact table has three date columns - transaction begin date, transaction end date, and updated date. When I initially designed the data source view my requirements only called for the trans begin and end dates so I made two joins to the time table from the sales table. I then ran the cube wizard which correctly detected that I intended to use the time table twice for two different Time hierarchies (Roles) on a single time dimension.

I have recently been asked to add the updated date but I cannot figure out how to add a new hierarchy (Role) on the time dimension. Any Ideas?

You should be able to go to the Dimension Usage tab in the cube designer and click on the Add Cube Dimension button on the toolbar (the third button from the left). When the list of dimensions comes up, simply select your Time dimension and add it again. You'll then see the dimension added within the list of dimensions down the left side of the tab, likely with a name like Time (Time 1) or something similar. Now, simply highlight the newly added role-playing dimension, hit F2, and rename it whatever you want. Then, of course, set the appropriate relationship between this new dimension and the measure group(s) within the cube...

HTH,

Dave Fackler

sql

New Tables and Stored procedures are saving as System Tables

When I try to create a new table/stored procedure, MSDE is saving it as
system table/procedure. And this is happening to all the databases on that
instance.
I checked the sysobjects table. The new tables are saved as Type 'U'.
But their status is in -ve nos.
What I can do to fix that?
Hi
You should not rely on using the "FOR MS USE" objects/columns. What does
OBJECTPROPERTY(id,'IsSystemTable') return?
John
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:45F7565B-FC35-437E-88CC-30E896875DF0@.microsoft.com...
> When I try to create a new table/stored procedure, MSDE is saving it as
> system table/procedure. And this is happening to all the databases on that
> instance.
> I checked the sysobjects table. The new tables are saved as Type 'U'.
> But their status is in -ve nos.
> What I can do to fix that?
>
>

New Tables and Stored procedures are saving as System Tables

When I try to create a new table/stored procedure, MSDE is saving it as
system table/procedure. And this is happening to all the databases on that
instance.
I checked the sysobjects table. The new tables are saved as Type 'U'.
But their status is in -ve nos.
What I can do to fix that?Hi
You should not rely on using the "FOR MS USE" objects/columns. What does
OBJECTPROPERTY(id,'IsSystemTable') return?
John
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:45F7565B-FC35-437E-88CC-30E896875DF0@.microsoft.com...
> When I try to create a new table/stored procedure, MSDE is saving it as
> system table/procedure. And this is happening to all the databases on that
> instance.
> I checked the sysobjects table. The new tables are saved as Type 'U'.
> But their status is in -ve nos.
> What I can do to fix that?
>
>

new table to ASPNetDb.mdf and foreign key

I was thinking of adding tables to ASPNetDB.mdf and have one of those tables have column userid as a foreign key from aspnet_Users

When I try to create relationship in Diagram, I get error saying that "data typ properties does not match"

userid in aspNet_Users is uniqueidentifier and userid (fk) in new table is int

What should I use, should I do that at all?

Thanks

uniqueidentifier and int is of different type. correct me if i'm wrong, the foreign key must be of same type as the primary key.|||

yes, but even when I use same type it gives me the same error

|||

Try to compare your new table with aspnet_Membership table, this table has an existing relationship with aspnet_Users table. Maybe you are missing a declaration of keys. As you can see both tables has UserID and they are tagged as a primary key and created a relationship based on that keys, but you can always have an option to make it an additional key instead of a primary.

Hope this helps.

|||That helps, good advice. I was able to connect keys from membership table with no problems. Thank you.

New table on server not replicating to device

Hi,

I have a SQL 2005 Mobile db merge synching to SQL Server 2005. I've been using this for a few months now and generally it works well (except when adding not null columns with default values, but that's another story...). I just added a new table to the server, went into the publication articles and checked the new table. It told me I would need to generate a new snapshot for the table, so I then generated a new snapshot. Then I synched the mobile db and it worked with no errors, but the new table I added on the server is not on the mobile db (I checked using the Query Analyzer). I have also tried reinitializing subscriptions, but this didn't work either. Any ideas why this table would not be synched to my mobile db (and no error message on mobile device)?

Regards,

Greg

Greg

If you create a new new subscription to the publication (eg a new device, or you could just create a test sdf file and use Sql management studio to sync it) does the table get sync'd? Have you checked the snapshot agent status to confirm that a new snapshot has been generated?

|||

Nick,

Sorry for the late reply, alerts not working for some reason. Yes, a new subscription picks up the new table. I confirmed that the snapshot agent did create a new snapshot. I've actually had more problems since that post, it seems that if I make any changes to the db schema I get replication errors and/or apparently successful replication (replication monitor shows success) without the schema changes. The only fix seems to be to delete the db on the subscriber and resync (basically a new subscription). After that it will sync fine, until I make any change to the schema on the server. Is it possible that there is some corruption occurring in the publisher merge tables? Do you know of any rebuilds/reindexes I can do (other than deleting the publication and setting up again)?

Regards,

Greg

sql

New table on server not replicating to device

Hi,

I have a SQL 2005 Mobile db merge synching to SQL Server 2005. I've been using this for a few months now and generally it works well (except when adding not null columns with default values, but that's another story...). I just added a new table to the server, went into the publication articles and checked the new table. It told me I would need to generate a new snapshot for the table, so I then generated a new snapshot. Then I synched the mobile db and it worked with no errors, but the new table I added on the server is not on the mobile db (I checked using the Query Analyzer). I have also tried reinitializing subscriptions, but this didn't work either. Any ideas why this table would not be synched to my mobile db (and no error message on mobile device)?

Regards,

Greg

Greg

If you create a new new subscription to the publication (eg a new device, or you could just create a test sdf file and use Sql management studio to sync it) does the table get sync'd? Have you checked the snapshot agent status to confirm that a new snapshot has been generated?

|||

Nick,

Sorry for the late reply, alerts not working for some reason. Yes, a new subscription picks up the new table. I confirmed that the snapshot agent did create a new snapshot. I've actually had more problems since that post, it seems that if I make any changes to the db schema I get replication errors and/or apparently successful replication (replication monitor shows success) without the schema changes. The only fix seems to be to delete the db on the subscriber and resync (basically a new subscription). After that it will sync fine, until I make any change to the schema on the server. Is it possible that there is some corruption occurring in the publisher merge tables? Do you know of any rebuilds/reindexes I can do (other than deleting the publication and setting up again)?

Regards,

Greg

New table added to p-t-p transactional publication does not replicate - why?

Hello,

We are new to replication and are testing it in our development environment. We have a peer-to-peer transactional publication on our three servers. The single table in the original publication replicated fine to the two subscribing servers. We next added a new table (article) to the publication. Adding it to the original publication worked fine but the table did not replicate to the other servers. (We previously had changed the schema of the original table and the schema changes replicated properly.) We attempted to recreate the snapsnot using the "View Snapshot Agent Status" option. Clicking the Start button resulted in the display of this message: "[0%] A snapshot was not generated because no subscriptions needed initialization."

This seems odd because a new table was added to the publication and Microsoft help states that the snapshot must be rebuilt. I have read other topics that refer to a @.immediate_sync property that must be set to zero. I'm not sure if this is our problem or even how to set this value. Meanwhile, the other servers, as viewed through the Replication Monitor, are complaining that their snapshots do not match the publication snapshot.

Can someone point me in the right direction?

Thanks,

BCB

Hi,

P2P replication should not be initialized using snapshot agent. The subscription must be initialized either manually or using backup/restore.

To add a new article into an existing P2P topology, following the instruction in this link (http://msdn2.microsoft.com/en-us/library/ms146867.aspx). Basically you need to ensure all nodes have the same schema/data for the table you would llike to add. And then call sp_addarticle at each node. P2P topology should be quiesced before it.

Thanks,

Peng

New SQL Datatypes

I started experimenting with SQL Server 2008, June CTP. So the first thing I tried to do is to create a new table having as column types the new datatypes available in Katmai. According to a MS white paper new sql data types include Filestream, Date, Time. Does June CTP include those new types? Am I making a wrong assumption?

Thank you

All the new datatypes are scheduled for a later release, so unfortunately we'll just have to wait.

The what's new section i BOL is pretty handy if you wonder what you can play with

|||As referred you might have to wait or see for next 2 CTP releases on the changes, if you have observed in Katmai BOL or not there are blank placeholders for such features inclusion or make them deprecated based upon the CONNECT feedback or by popularity.sql

New SP2 Excel Render Bug (Excel Formulas)

Hi,
I have a report that converts expression to Excel formulas. The Excel
formulas in a table are incorrectly rendered in a way that the references
are all off by 6 or so cells. When I set the "Omit Formulas" device setting
to true the values are correct, though there are no formulas. I don't
remember this failure in SP1.
Thanks,
Bryan
PS. Is there a way to make "OmitFormulas=true" static when the request is
made from the report manager?BTW, This bug only happens when requesting the render from the report
server. It doesn't happen in VS.NET.
"BDB" <reply@.to.group.com> wrote in message
news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a report that converts expression to Excel formulas. The Excel
> formulas in a table are incorrectly rendered in a way that the references
> are all off by 6 or so cells. When I set the "Omit Formulas" device
> setting to true the values are correct, though there are no formulas. I
> don't remember this failure in SP1.
> Thanks,
> Bryan
> PS. Is there a way to make "OmitFormulas=true" static when the request is
> made from the report manager?
>|||Setting the DeviceInfo defaults has been added in SQL 2005 RS but is not
available with SQL 2000. As for the bug, nothing has changed in this area so
I would imagine it happened with SP1 as well.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"BDB" <reply@.to.group.com> wrote in message
news:eoGuJAoSFHA.3720@.TK2MSFTNGP10.phx.gbl...
> BTW, This bug only happens when requesting the render from the report
> server. It doesn't happen in VS.NET.
> "BDB" <reply@.to.group.com> wrote in message
> news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a report that converts expression to Excel formulas. The Excel
>> formulas in a table are incorrectly rendered in a way that the references
>> are all off by 6 or so cells. When I set the "Omit Formulas" device
>> setting to true the values are correct, though there are no formulas. I
>> don't remember this failure in SP1.
>> Thanks,
>> Bryan
>> PS. Is there a way to make "OmitFormulas=true" static when the request
>> is made from the report manager?
>|||No, I've tested both ways. This bug was introduced in SP2.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ePzjhGpSFHA.2324@.TK2MSFTNGP10.phx.gbl...
> Setting the DeviceInfo defaults has been added in SQL 2005 RS but is not
> available with SQL 2000. As for the bug, nothing has changed in this area
> so I would imagine it happened with SP1 as well.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "BDB" <reply@.to.group.com> wrote in message
> news:eoGuJAoSFHA.3720@.TK2MSFTNGP10.phx.gbl...
>> BTW, This bug only happens when requesting the render from the report
>> server. It doesn't happen in VS.NET.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a report that converts expression to Excel formulas. The Excel
>> formulas in a table are incorrectly rendered in a way that the
>> references are all off by 6 or so cells. When I set the "Omit Formulas"
>> device setting to true the values are correct, though there are no
>> formulas. I don't remember this failure in SP1.
>> Thanks,
>> Bryan
>> PS. Is there a way to make "OmitFormulas=true" static when the request
>> is made from the report manager?
>>
>|||Can you post or send me your RDL (remove the online. part in my e-mail
address)?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"BDB" <reply@.to.group.com> wrote in message
news:uFBYKQqSFHA.3552@.TK2MSFTNGP10.phx.gbl...
> No, I've tested both ways. This bug was introduced in SP2.
>
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ePzjhGpSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>> Setting the DeviceInfo defaults has been added in SQL 2005 RS but is not
>> available with SQL 2000. As for the bug, nothing has changed in this area
>> so I would imagine it happened with SP1 as well.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eoGuJAoSFHA.3720@.TK2MSFTNGP10.phx.gbl...
>> BTW, This bug only happens when requesting the render from the report
>> server. It doesn't happen in VS.NET.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a report that converts expression to Excel formulas. The Excel
>> formulas in a table are incorrectly rendered in a way that the
>> references are all off by 6 or so cells. When I set the "Omit
>> Formulas" device setting to true the values are correct, though there
>> are no formulas. I don't remember this failure in SP1.
>> Thanks,
>> Bryan
>> PS. Is there a way to make "OmitFormulas=true" static when the request
>> is made from the report manager?
>>
>>
>|||Sure. Thank you.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:%2376cRJrSFHA.3300@.TK2MSFTNGP10.phx.gbl...
> Can you post or send me your RDL (remove the online. part in my e-mail
> address)?
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "BDB" <reply@.to.group.com> wrote in message
> news:uFBYKQqSFHA.3552@.TK2MSFTNGP10.phx.gbl...
>> No, I've tested both ways. This bug was introduced in SP2.
>>
>> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
>> news:ePzjhGpSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>> Setting the DeviceInfo defaults has been added in SQL 2005 RS but is not
>> available with SQL 2000. As for the bug, nothing has changed in this
>> area so I would imagine it happened with SP1 as well.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eoGuJAoSFHA.3720@.TK2MSFTNGP10.phx.gbl...
>> BTW, This bug only happens when requesting the render from the report
>> server. It doesn't happen in VS.NET.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a report that converts expression to Excel formulas. The Excel
>> formulas in a table are incorrectly rendered in a way that the
>> references are all off by 6 or so cells. When I set the "Omit
>> Formulas" device setting to true the values are correct, though there
>> are no formulas. I don't remember this failure in SP1.
>> Thanks,
>> Bryan
>> PS. Is there a way to make "OmitFormulas=true" static when the
>> request is made from the report manager?
>>
>>
>>
>|||This issue was resolved by rebuilding and redeploying the RDL.
Much thinks to Brian Welcker for his help.
Bryan
"BDB" <reply@.to.group.com> wrote in message
news:OZrPaFzSFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Sure. Thank you.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:%2376cRJrSFHA.3300@.TK2MSFTNGP10.phx.gbl...
>> Can you post or send me your RDL (remove the online. part in my e-mail
>> address)?
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:uFBYKQqSFHA.3552@.TK2MSFTNGP10.phx.gbl...
>> No, I've tested both ways. This bug was introduced in SP2.
>>
>> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
>> news:ePzjhGpSFHA.2324@.TK2MSFTNGP10.phx.gbl...
>> Setting the DeviceInfo defaults has been added in SQL 2005 RS but is
>> not available with SQL 2000. As for the bug, nothing has changed in
>> this area so I would imagine it happened with SP1 as well.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eoGuJAoSFHA.3720@.TK2MSFTNGP10.phx.gbl...
>> BTW, This bug only happens when requesting the render from the report
>> server. It doesn't happen in VS.NET.
>> "BDB" <reply@.to.group.com> wrote in message
>> news:eK%234l6nSFHA.3336@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I have a report that converts expression to Excel formulas. The
>> Excel formulas in a table are incorrectly rendered in a way that the
>> references are all off by 6 or so cells. When I set the "Omit
>> Formulas" device setting to true the values are correct, though there
>> are no formulas. I don't remember this failure in SP1.
>> Thanks,
>> Bryan
>> PS. Is there a way to make "OmitFormulas=true" static when the
>> request is made from the report manager?
>>
>>
>>
>>
>

New SP vs parameters

I have a table of 25-30 million properties, from which are retrieved
~150 centered on a point, based on the parameters -- coordinates,
property type and date of transaction. There's an SP (also implemented
as a function returning a table) to return the desired records.

This look-up takes the most time in the C# program that calls it, and
should be optimized. It was suggested that instead of having an SP on
the server, each time the program should create an SP that is the same,
however without any parameters -- with the values hard-coded. Then
execute it, and drop it. This way, the execution plan will be
customized for the specific parameters. I tried it and it turns out
the suggested method is noticeably faster, even compared to recompiling
an SP every time. I was wondering if there is a way to get equivalent
performance out of an SP or UDF that has parameters, or is this
approach necessarily going to be less optimized than hard-coded
non-parameters.

Thanks,
JimHi Jim,
Okay.
Does the SP takes a long time to run as stand alone (executing it on
the server without calling from C#)?
Is it too much of a trouble to post the whole code? Can you time the SP
and what is it?
As a quick solution maybe you can modify the SP to save the result into
a table and return some number instead (as success). Then the C# just
access that table and once done drop it at the end. You can have a
recompile option when creating the stored procedure but might worth as
well if you can increase performance by minor fixes.

jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

I have a table of 25-30 million properties, from which are retrieved
~150 centered on a point, based on the parameters -- coordinates,
property type and date of transaction. There's an SP (also implemented
as a function returning a table) to return the desired records.
>
This look-up takes the most time in the C# program that calls it, and
should be optimized. It was suggested that instead of having an SP on
the server, each time the program should create an SP that is the same,
however without any parameters -- with the values hard-coded. Then
execute it, and drop it. This way, the execution plan will be
customized for the specific parameters. I tried it and it turns out
the suggested method is noticeably faster, even compared to recompiling
an SP every time. I was wondering if there is a way to get equivalent
performance out of an SP or UDF that has parameters, or is this
approach necessarily going to be less optimized than hard-coded
non-parameters.
>
Thanks,
Jim

|||Your answer is: "it depends."

Instead of re-creating the stored proc, you can use the WITH RECOMPILE
option, which will probably do what you want:

CREATE PROC dbo.foo WITH RECOMPILE AS
select 'hello world';

http://msdn2.microsoft.com/en-us/li...59(SQL.80).aspx
-Dave

jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

I have a table of 25-30 million properties, from which are retrieved
~150 centered on a point, based on the parameters -- coordinates,
property type and date of transaction. There's an SP (also implemented
as a function returning a table) to return the desired records.
>
This look-up takes the most time in the C# program that calls it, and
should be optimized. It was suggested that instead of having an SP on
the server, each time the program should create an SP that is the same,
however without any parameters -- with the values hard-coded. Then
execute it, and drop it. This way, the execution plan will be
customized for the specific parameters. I tried it and it turns out
the suggested method is noticeably faster, even compared to recompiling
an SP every time. I was wondering if there is a way to get equivalent
performance out of an SP or UDF that has parameters, or is this
approach necessarily going to be less optimized than hard-coded
non-parameters.
>
Thanks,
Jim

new records in table

i have a situation where i have table A with 50 records, )with no timestamp column). Data will be appended to it on a constant basis, daily. how can you filter out the new records?

Can you please post the schema of the table or a sample of it? Does it have an identity column for example? If so, you could capture the MAX identity value after every day and determine the new rows. But this will only give rows that were added not modified. You can use similar approach if you have a key that has some time part in it. But this all depends on your data. Often it is best to use staging table approach so that you don't have to go to the main table(s). You can insert first into the staging table and then update the main table. This will allow you to do additional processing using the rows from the staging table.

New record in table already or not when in an INSTEAD OF trigger?

Hi, friends,
I am writting a INSTEAD OF INSERT trigger for an insert action on Users
table of our SQL Server 2000 DB.
I know there is only one record (the new inserted one) in inserted table,
but, is this new record also already in Users table? In anther word, if I do
a query in the trigger like:
DECLARE @.newUserID INT
SELECT @.newUserID = i.userID FROM inserted AS i
SELECT * FROM Users WHERE userID = @.newUserID
should I get a record back for the last SELECT statement, not yet? Any
reference paper?
Thanks for your help.No, the row that is in the inserted table in the INSTEAD OF TRIGGER is _not_
already in the base table. And if you don't explicitly insert the row in the
base table inside your trigger, the row will never be inserted. If you want
the behaviour you describe, you should use an AFTER trigger.
Jacco Schalkwijk
SQL Server MVP
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:22E7E45F-E28D-4D21-8E95-67C235390200@.microsoft.com...
> Hi, friends,
> I am writting a INSTEAD OF INSERT trigger for an insert action on Users
> table of our SQL Server 2000 DB.
> I know there is only one record (the new inserted one) in inserted table,
> but, is this new record also already in Users table? In anther word, if I
> do
> a query in the trigger like:
> DECLARE @.newUserID INT
> SELECT @.newUserID = i.userID FROM inserted AS i
> SELECT * FROM Users WHERE userID = @.newUserID
> should I get a record back for the last SELECT statement, not yet? Any
> reference paper?
> Thanks for your help.
>|||On Thu, 24 Mar 2005 14:37:04 -0800, Andrew wrote:

>I am writting a INSTEAD OF INSERT trigger for an insert action on Users
>table of our SQL Server 2000 DB.
>I know there is only one record (the new inserted one) in inserted table,
(snip)
Hi Andrew,
This is only true for single-row inserts. If you execute an INSERT INTO
... SELECT ... command, ALL new rows will be in the inserted table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

New problem getting data from xml table in sql db (Newbie)

Hello,

I have this xml tablerow in a table in my db.

2 examples

<Toyconfiguration version = "1" name = "Lego">

<Ballconfiguration version = "2020" name= "Adidas">

If I would want to get the name of the toy or ball I would write

Select

Tablerow.value('((@.name)[1]', 'varchar(100)')

From table

This would return Lego and Adidas

But what if I want to know which kind of configuration it is? How do I do this?

The answer I want is Toyconfiguration and Ballconfiguration..

Any help would be appreciated!

You could use local-name() function:

select

Tablerow.xml_col.value('(//@.name)[1]', 'varchar(100)')

,Tablerow.xml_col.value('local-name((*[@.name])[1])', 'varchar(100)')

from TableRow

|||

Kontantin,

Thanks a bunch for your answer. You saved me!!!

new line in bcp

I am exporting sql server table data to excel using bcp... But if any field
contain carrige return , that fields break up into to next line. Is their
any solution to itOne other way might be to create an ODBC connection to Excel and export
through this data source
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Vikram" <aa@.aa> wrote in message
news:ew#myvQPGHA.1532@.TK2MSFTNGP12.phx.gbl...
> I am exporting sql server table data to excel using bcp... But if any
field
> contain carrige return , that fields break up into to next line. Is their
> any solution to it
>|||If you do not want the carriage returns in the data, create a view
that returns all the columns of the table but removes the carriage
returns from the particular column(s) using REPLACE(). Then BCP out
from the view, instead of from the table.
If you must preserve the carriage returns in the data, don't use BCP.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Mar 2006 14:11:29 +0530, "Vikram" <aa@.aa> wrote:

>I am exporting sql server table data to excel using bcp... But if any field
>contain carrige return , that fields break up into to next line. Is their
>any solution to it|||But I cant use views as I am calling stored procedure from bcp... And stored
procedure return data from temp tables...
Is there is no way by which bcp can preserve carrige return in the data.
Because when we use DTS, it export the data perfectly. I even cannot use DTS
as sp return data from temp table...
Any suggestion will be helpful?
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:uubc02t9b70m4qtavu7cohq4vmuvi1oqlr@.
4ax.com...
> If you do not want the carriage returns in the data, create a view
> that returns all the columns of the table but removes the carriage
> returns from the particular column(s) using REPLACE(). Then BCP out
> from the view, instead of from the table.
> If you must preserve the carriage returns in the data, don't use BCP.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Mar 2006 14:11:29 +0530, "Vikram" <aa@.aa> wrote:
>
field|||Vikram (aa@.aa) writes:
> But I cant use views as I am calling stored procedure from bcp... And
> stored procedure return data from temp tables...
You using the queryout option? Anyway, if you are already using temp
tables, you have all possibilities to modify the data.

> Is there is no way by which bcp can preserve carrige return in the data.
I guess the question is not how get BCP to preserve the CR in the data -
it bulks out whatever that is, but how to a file should look like for
Excel to accept it with the newlines preserved. I don't know Excel well
enough to say how the file should look like.
Once you are equipped with that knowledge, you can address this by either
formatting the data when you select it, or use a format file, to have BCP
to do it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

New Install can't view data

I installed MSSQL 2000 Dev version. It appeared to work. I am now
trying to open any table in Enterprise manager and I get "Provider
cannot be found. It may not be properly installed." I don't see any
errors in Event Viewer.
I'm running it on XP Pro.
Thanks.
--
_________________________________________
Don Gollahon
dlgllhn@.InHisStepsSoftware.com
ICQ#: 115831669
MSN Msgr: dlgllhn@.theinter.com
"What in Eternity does it matter?"
_________________________________________Don Gollahon wrote:
> I installed MSSQL 2000 Dev version. It appeared to work. I am now
> trying to open any table in Enterprise manager and I get "Provider
> cannot be found. It may not be properly installed." I don't see any
> errors in Event Viewer.
> I'm running it on XP Pro.
> Thanks.
Have you installed SP3a or SP4 yet? The problem you describe is not one
I know of with the RTM version, but applying the service pack may
address any problems.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:
> Don Gollahon wrote:
> > I installed MSSQL 2000 Dev version. It appeared to work. I am now
> > trying to open any table in Enterprise manager and I get "Provider
> > cannot be found. It may not be properly installed." I don't see
> > any errors in Event Viewer.
> >
> > I'm running it on XP Pro.
> >
> > Thanks.
> Have you installed SP3a or SP4 yet? The problem you describe is not
> one I know of with the RTM version, but applying the service pack may
> address any problems.
I just installed SP3a and have the same result.
--
_________________________________________
Don Gollahon
dlgllhn@.InHisStepsSoftware.com
ICQ#: 115831669
MSN Msgr: dlgllhn@.theinter.com
"What in Eternity does it matter?"
_________________________________________

New Install can't view data

I installed MSSQL 2000 Dev version. It appeared to work. I am now
trying to open any table in Enterprise manager and I get "Provider
cannot be found. It may not be properly installed." I don't see any
errors in Event Viewer.
I'm running it on XP Pro.
Thanks.
_________________________________________
Don Gollahon
dlgllhn@.InHisStepsSoftware.com
ICQ#: 115831669
MSN Msgr: dlgllhn@.theinter.com
"What in Eternity does it matter?"
_________________________________________
Don Gollahon wrote:
> I installed MSSQL 2000 Dev version. It appeared to work. I am now
> trying to open any table in Enterprise manager and I get "Provider
> cannot be found. It may not be properly installed." I don't see any
> errors in Event Viewer.
> I'm running it on XP Pro.
> Thanks.
Have you installed SP3a or SP4 yet? The problem you describe is not one
I know of with the RTM version, but applying the service pack may
address any problems.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David Gugick wrote:

> Don Gollahon wrote:
> Have you installed SP3a or SP4 yet? The problem you describe is not
> one I know of with the RTM version, but applying the service pack may
> address any problems.
I just installed SP3a and have the same result.
_________________________________________
Don Gollahon
dlgllhn@.InHisStepsSoftware.com
ICQ#: 115831669
MSN Msgr: dlgllhn@.theinter.com
"What in Eternity does it matter?"
_________________________________________

New Install can't view data

I installed MSSQL 2000 Dev version. It appeared to work. I am now
trying to open any table in Enterprise manager and I get "Provider
cannot be found. It may not be properly installed." I don't see any
errors in Event Viewer.
I'm running it on XP Pro.
Thanks.
________________________________________
_
Don Gollahon
dlgllhn@.InHisStepsSoftware.com
ICQ#: 115831669
MSN Msgr: dlgllhn@.theinter.com
"What in Eternity does it matter?"
________________________________________
_Don Gollahon wrote:
> I installed MSSQL 2000 Dev version. It appeared to work. I am now
> trying to open any table in Enterprise manager and I get "Provider
> cannot be found. It may not be properly installed." I don't see any
> errors in Event Viewer.
> I'm running it on XP Pro.
> Thanks.
Have you installed SP3a or SP4 yet? The problem you describe is not one
I know of with the RTM version, but applying the service pack may
address any problems.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David Gugick wrote:

> Don Gollahon wrote:
> Have you installed SP3a or SP4 yet? The problem you describe is not
> one I know of with the RTM version, but applying the service pack may
> address any problems.
I just installed SP3a and have the same result.
________________________________________
_
Don Gollahon
dlgllhn@.InHisStepsSoftware.com
ICQ#: 115831669
MSN Msgr: dlgllhn@.theinter.com
"What in Eternity does it matter?"
________________________________________
_