can't get diagram pane to show in query analyzer (sql 2000)

I'm trying to use SQL query analyzer and I can't get the diagram pane to show
or the grid pane.
Kaz,
Is this a table? Does this happen for other tables? How about a new table?
Do you have permissions to the table?
"kaz via droptable.com" <u14299@.uwe> wrote in message
news:54f5768b18eb0@.uwe...
> I'm trying to use SQL query analyzer and I can't get the diagram pane to
> show
> or the grid pane.
|||I go into query analyzer from start - programs - Microsoft SQL Server -
query analyzer and connect to the database to start a new query.
If I go into Enterprise manager and go to open a table using Query I get all
4 panes -Diagram,Grid,SQL, and the Results pan.
Jerry Spivey wrote:[vbcol=seagreen]
>Kaz,
>Is this a table? Does this happen for other tables? How about a new table?
>Do you have permissions to the table?
|||Steve,
If I undestand you correctly...that functionality is only available via EM
and not QA.
HTH
Jerry
"Steve Kasprzak via droptable.com" <u14299@.uwe> wrote in message
news:54f5af2c3c87c@.uwe...[vbcol=seagreen]
>I go into query analyzer from start - programs - Microsoft SQL Server -
> query analyzer and connect to the database to start a new query.
> If I go into Enterprise manager and go to open a table using Query I get
> all
> 4 panes -Diagram,Grid,SQL, and the Results pan.
> Jerry Spivey wrote:
|||Yes.

can't get diagram pane to show in query analyzer (sql 2000)

I'm trying to use SQL query analyzer and I can't get the diagram pane to sho
w
or the grid pane.Kaz,
Is this a table? Does this happen for other tables? How about a new table?
Do you have permissions to the table?
"kaz via droptable.com" <u14299@.uwe> wrote in message
news:54f5768b18eb0@.uwe...
> I'm trying to use SQL query analyzer and I can't get the diagram pane to
> show
> or the grid pane.|||I go into query analyzer from start - programs - Microsoft SQL Server -
query analyzer and connect to the database to start a new query.
If I go into Enterprise manager and go to open a table using Query I get all
4 panes -Diagram,Grid,SQL, and the Results pan.
Jerry Spivey wrote:[vbcol=seagreen]
>Kaz,
>Is this a table? Does this happen for other tables? How about a new table
?
>Do you have permissions to the table?|||Steve,
If I undestand you correctly...that functionality is only available via EM
and not QA.
HTH
Jerry
"Steve Kasprzak via droptable.com" <u14299@.uwe> wrote in message
news:54f5af2c3c87c@.uwe...[vbcol=seagreen]
>I go into query analyzer from start - programs - Microsoft SQL Server -
> query analyzer and connect to the database to start a new query.
> If I go into Enterprise manager and go to open a table using Query I get
> all
> 4 panes -Diagram,Grid,SQL, and the Results pan.
> Jerry Spivey wrote:|||Yes.

can't get diagram pane to show in query analyzer (sql 2000)

I'm trying to use SQL query analyzer and I can't get the diagram pane to show
or the grid pane.Kaz,
Is this a table? Does this happen for other tables? How about a new table?
Do you have permissions to the table?
"kaz via SQLMonster.com" <u14299@.uwe> wrote in message
news:54f5768b18eb0@.uwe...
> I'm trying to use SQL query analyzer and I can't get the diagram pane to
> show
> or the grid pane.|||I go into query analyzer from start - programs - Microsoft SQL Server -
query analyzer and connect to the database to start a new query.
If I go into Enterprise manager and go to open a table using Query I get all
4 panes -Diagram,Grid,SQL, and the Results pan.
Jerry Spivey wrote:
>Kaz,
>Is this a table? Does this happen for other tables? How about a new table?
>Do you have permissions to the table?
>> I'm trying to use SQL query analyzer and I can't get the diagram pane to
>> show
>> or the grid pane.|||Steve,
If I undestand you correctly...that functionality is only available via EM
and not QA.
HTH
Jerry
"Steve Kasprzak via SQLMonster.com" <u14299@.uwe> wrote in message
news:54f5af2c3c87c@.uwe...
>I go into query analyzer from start - programs - Microsoft SQL Server -
> query analyzer and connect to the database to start a new query.
> If I go into Enterprise manager and go to open a table using Query I get
> all
> 4 panes -Diagram,Grid,SQL, and the Results pan.
> Jerry Spivey wrote:
>>Kaz,
>>Is this a table? Does this happen for other tables? How about a new
>>table?
>>Do you have permissions to the table?
>> I'm trying to use SQL query analyzer and I can't get the diagram pane to
>> show
>> or the grid pane.|||Yes.

Cant fit results of tsql query in excel

Hi,

I am using a .dqy file to import some rows from a sql server 2003
database to microsoft excel. Unfortunately, I have am importing more
rows than excel can handle. Is there a way around this?

It appears that there is no equivalent of the LIMIT(offset, number of
rows to return) function of mysql in tsql. Had there been an
equivalent, I would have created multiple .dqy files, which would query
the database for pages of results as appropriate. There is an upper
limit to the number of records that there can be in the database, so
this would have been a feasible solution.

Also, I must use .dqy files (or something equivalen) because the
queries are invoked from a website, and it is necessary to download the
results, sort/filter, etc. (in excel).

Thanks for any suggestions.imagine if you will a world where you get all the data you ever wanted
with subsecond response times.
you can slice and dice all of your data, sort the results, and
otherwise find exceptional values in an instant, no matter how large
your data is.

The answer is OLAP.

Barring that, you can use a reporting tool like Crystal.

Barring that, well, hmmmm.|||Thanks for your opinions.

Unfortunately, my reports are for non-technical users, otherwise I
wouldn't even need to bother with all of this - they could have queried
the sql database directly. However, your comments are appreciated.|||RA wrote:
> Thanks for your opinions.
> Unfortunately, my reports are for non-technical users, otherwise I
> wouldn't even need to bother with all of this - they could have queried
> the sql database directly. However, your comments are appreciated.

Non-technical users are exactly the audience that the BI tools are
designed for. I recommend you check out that option. There are much
richer, easier and cheaper solutions than the "dump the database in
Excel" method.

That said, it is perfectly possible to segment the data in the manner
you are proposing. In SQL Server 2005 you can make use of the
ROW_NUMBER() function to filter results. For earlier versions take a
look at: http://www.aspfaq.com/show.asp?id=2120.

BTW there is no SQL Server 2003. I expect you mean 2000.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Ok, thanks for the advice guys.

I meant SQL server 2000 (it was a typo). It now appears that the
requirement is no longer critical - the users are happy to be taught
how to query with SQL, until office 12 comes out (excel will have a row
limit of about a million [2^20])sql

Can't find objects

I have a database with a bunch of objects owned by a user named, "tims". I a
m
logged into SQL Query Analyzer as the tims user, but I can't locate objects
unless I use I qualify them with the owner name. For example,
exec sp_select_corridors #doesn't work
exec tims.sp_selelct_corridors #works
BTW, this was working before. I recently did a DTS import on another
database on this server. I am wondering if the tims login got messed up. If
so, how would I fix it?
Thanks!
- JohnnyI would drop the objects and re-create them, specifying dbo. as the owner...
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:8E5E39D7-E65D-44CB-81CE-DD5324A475FA@.microsoft.com...
>I have a database with a bunch of objects owned by a user named, "tims". I
>am
> logged into SQL Query Analyzer as the tims user, but I can't locate
> objects
> unless I use I qualify them with the owner name. For example,
> exec sp_select_corridors #doesn't work
> exec tims.sp_selelct_corridors #works
> BTW, this was working before. I recently did a DTS import on another
> database on this server. I am wondering if the tims login got messed up.
> If
> so, how would I fix it?
> Thanks!
> - Johnny|||Can you check if there is another sp with the same name?

> exec sp_select_corridors #doesn't work
Any error?
AMB
"JohnnyMagz" wrote:

> I have a database with a bunch of objects owned by a user named, "tims". I
am
> logged into SQL Query Analyzer as the tims user, but I can't locate object
s
> unless I use I qualify them with the owner name. For example,
> exec sp_select_corridors #doesn't work
> exec tims.sp_selelct_corridors #works
> BTW, this was working before. I recently did a DTS import on another
> database on this server. I am wondering if the tims login got messed up. I
f
> so, how would I fix it?
> Thanks!
> - Johnny

can't find Enterprise Manager and Query Analyzer after setup

I downloaded the trial version of sql server 2005. but after installation, I couldnt find the Enterprise Manager and Query Analyzer. Any idea about that? Thanks.

Hi zhishan,

I believe those services can be configured from control panel (turn windows features on or off). Check the boxes next to enterprise manager services and query analyzer, then ok, and windows wil configure and update for those services.

|||Thanks djartsinc - do you know where on the control panel has the function? I spent a long time trying to find, but couldn't...|||

Which Edition of SQL Server 2005 do you download? If it is SQL Server 2005 Express, such tool is not included. Please try SQL Server 2005 Expree with Advanced Services, which includes tools. You can get it from the same download website as SQL Server 2005 Express.

Cant figure out this query

I have a table with multiple records.

Some have the same value in the 'subkey' field.
I want to select all the records from the table that have their highest MAINKEY.

So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)

Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.

So the tables looks sort of lLike this:

ID SK MK
1 10 2
2 10 3
3 25 2
4 25 3

I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.

I can not figure this out. :eek:

Any help would be GREATLY appreciated.This works...

SELECT [ID]
FROM yourtable T1
WHERE EXISTS (
SELECT SK, MAX(MK) AS MK
FROM yourtable T2
WHERE T1.SK=T2.SK
GROUP BY SK
HAVING T1.MK=MAX(T2.MK))|||select a.id, a.sk, a.mk from yourtable a
where a.mk in(select max(b.mk) from yourtable b
where a.sk = b.sk)|||Simpler even:

select a.sk, max(a.mk) as MK from yourtable a
group by a.sk

cant figure out how to write query..

Hi,
I have 3 tables, a person table, a timeRecords table, and a
RegionPersonHistory table.
The timeRecords table holds how many days were worked for a particualr date,
and the RegionPersonHistory keeps track of the persons Region. People can be
allocated to work on different regions so they might be working in the US fo
r
3 days then the following 4 days are in Europe etc.
I am trying to figure out how to write a query that will calulate how many
days were worked in any one region of a any specific date period. The tricky
thing is that the RegionPersonHistory table only holds records for a person
if they change from their default region (their default region is held in th
e
Person table). It doesnt always hold records for the person.
Have a look at the sql below which sets up the tables and see if you
understand my problem.
Here is the sql for the tables and some sample data...
CREATE TABLE [SYSDBA].[RegionPersonHistory] (
[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startdate] [datetime] NOT NULL ,
[enddate] [datetime] NOT NULL ,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
go
CREATE TABLE [SYSDBA].[TimeRecords] (
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[startdate] [datetime] NOT NULL ,
[days] [integer] NOT NULL,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SYSDBA].[Person] (
[Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[key] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
go
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-01',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-02',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-03',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-04',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-05',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-06',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-07',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-08',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-09',1
insert [SYSDBA].[TimeRecords]
select 'smithd','2006-01-10',1
go
insert [SYSDBA].[Person]
select 'smithd','US'
go
insert [SYSDBA].[RegionPersonHistory]
select 'smithd','Europe','2006-01-04','2006-01-08'Can you give a sample result that you might need from the inputs?
"NH" wrote:

> Hi,
> I have 3 tables, a person table, a timeRecords table, and a
> RegionPersonHistory table.
> The timeRecords table holds how many days were worked for a particualr dat
e,
> and the RegionPersonHistory keeps track of the persons Region. People can
be
> allocated to work on different regions so they might be working in the US
for
> 3 days then the following 4 days are in Europe etc.
> I am trying to figure out how to write a query that will calulate how many
> days were worked in any one region of a any specific date period. The tric
ky
> thing is that the RegionPersonHistory table only holds records for a perso
n
> if they change from their default region (their default region is held in
the
> Person table). It doesnt always hold records for the person.
> Have a look at the sql below which sets up the tables and see if you
> understand my problem.
> Here is the sql for the tables and some sample data...
> CREATE TABLE [SYSDBA].[RegionPersonHistory] (
> [Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [startdate] [datetime] NOT NULL ,
> [enddate] [datetime] NOT NULL ,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> go
> CREATE TABLE [SYSDBA].[TimeRecords] (
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [startdate] [datetime] NOT NULL ,
> [days] [integer] NOT NULL,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [SYSDBA].[Person] (
> [Persid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Region] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [key] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> go
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-01',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-02',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-03',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-04',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-05',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-06',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-07',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-08',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-09',1
> insert [SYSDBA].[TimeRecords]
> select 'smithd','2006-01-10',1
> go
> insert [SYSDBA].[Person]
> select 'smithd','US'
> go
> insert [SYSDBA].[RegionPersonHistory]
> select 'smithd','Europe','2006-01-04','2006-01-08'|||Hello again Omnibuzz,
well, if I was to try to calculate the sum(days) that were worked in the US
region between 2006-01-01 and 2006-01-10 I would find that difficult because
theres a few days there that person 'smithd' logged while he was allocated t
o
the 'Europe' region. His default region is the US but the query needs to
check to see if any of the days logged between thoese dates were actually
part of a different region.
Those this make sense?
"Omnibuzz" wrote:
> Can you give a sample result that you might need from the inputs?
> "NH" wrote:
>|||Try this and let me know if this was what you wanted.
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-04'
select a.Persid,coalesce(b.region,c.region), count(a.days)
from Person c, TimeRecords a left outer join RegionPersonHistory b on
a.startdate between b.startdate and b.enddate
and a.persid = b.persid
where a.startdate between @.a and @.b
and a.persid = c.persid
group by a.persid,coalesce(b.region,c.region)|||Thats not quite right, that returns 8 days when it should be only 4.
Then also the query needs to take in a thrid paramter to filter for a
particualr region.
Maybe this is just a bit too messy...
"Omnibuzz" wrote:

> Try this and let me know if this was what you wanted.
>
> declare @.a datetime, @.b datetime
> set @.a = '2006-01-01'
> set @.b = '2006-01-04'
> select a.Persid,coalesce(b.region,c.region), count(a.days)
> from Person c, TimeRecords a left outer join RegionPersonHistory b on
> a.startdate between b.startdate and b.enddate
> and a.persid = b.persid
> where a.startdate between @.a and @.b
> and a.persid = c.persid
> group by a.persid,coalesce(b.region,c.region)|||It worked fine for the data you gave.
Can you give the data for which the error and tell me what are the filters
and what is the expected result
"NH" wrote:
> Thats not quite right, that returns 8 days when it should be only 4.
> Then also the query needs to take in a thrid paramter to filter for a
> particualr region.
> Maybe this is just a bit too messy...
> "Omnibuzz" wrote:
>|||sorry your query does return the same value I get.
I gave you a mistake in the source data, can you run this...
delete from RegionPersonHistory
insert [SYSDBA].[RegionPersonHistory]
select 'Europe','smithd','2006-01-04','2006-01-08'
I have this modified query now...
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-05'
select a.Persid, count(a.days)
from Person c, TimeRecords a
left join RegionPersonHistory b on (a.startdate between b.startdate and
b.enddate
and b.region='us')
where a.startdate between @.a and @.b
and a.persid = c.persid
and c.region='us'
group by a.persid
I am trying to only return days worked in the US... but cant figure it out..
.
"Omnibuzz" wrote:
> It worked fine for the data you gave.
> Can you give the data for which the error and tell me what are the filters
> and what is the expected result
> "NH" wrote:
>|||Okay, this works for me. You added the filter wrong.
I have changed my first query. And I saw the mitake in the insert and I
changed it :)
Check this and let me know if this works.
declare @.a datetime, @.b datetime
set @.a = '2006-01-01'
set @.b = '2006-01-05'
select a.Persid,coalesce(b.region,c.region), count(a.days)
from Person c, TimeRecords a left outer join RegionPersonHistory b on
a.startdate between b.startdate and b.enddate
and a.persid = b.persid
where a.startdate between @.a and @.b
and a.persid = c.persid
and coalesce(b.region,c.region) = 'us'
group by a.persid,coalesce(b.region,c.region)|||Thanks Omnibuzz, it looks like this is working.
I appreciate your help once again.
NH
"Omnibuzz" wrote:

> Okay, this works for me. You added the filter wrong.
> I have changed my first query. And I saw the mitake in the insert and I
> changed it :)
> Check this and let me know if this works.
>
> declare @.a datetime, @.b datetime
> set @.a = '2006-01-01'
> set @.b = '2006-01-05'
> select a.Persid,coalesce(b.region,c.region), count(a.days)
> from Person c, TimeRecords a left outer join RegionPersonHistory b on
> a.startdate between b.startdate and b.enddate
> and a.persid = b.persid
> where a.startdate between @.a and @.b
> and a.persid = c.persid
> and coalesce(b.region,c.region) = 'us'
> group by a.persid,coalesce(b.region,c.region)
>

Can't figure out how to write query

I have a table TABLE1. My company has 2 sites. This table contains employees
with the amount of hours they worked on which project at which sites.

code:

CREATE TABLE #TABLE1 (
Calldate varchar(10) NULL,
Employee varchar(10) NULL,
Project varchar(10) NULL,
Hours decimal(10,4) NULL,
Site varchar(1) NULL)
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '123', 'EAUD5', 2.5, '2')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '246', 'EACQ5', 3, '2')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ5', 2, '1')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 1.5, '1')
INSERT #TABLE1 (calldate, employee, project, hours, site)
VALUES ('20060217', '369', 'EACQ6', 5, '2')

I need to figure out the following:
I need the total hours of employees from both sites ONLY if they worked on a
project that ended in a 5. If employees worked on projects that did not end
in 5 I need the totals for their site only. A parameter of site will be
passed to the stored procedure.
So for example: If site parameter of 1 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 1.5
If site parameter of 2 is passed.
I need to see the following results:
Calldate Project TotalHours
20060217 EAUD5 2.5
20060217 EACQ5 5
20060217 EACQ6 5
Any help would be greatly appreciated,
Thanks,
ninel
Message posted via http://www.webservertalk.comThanks for posting DDL and sample data.
declare @.site varchar(1)
set @.site = '1'
select calldate, project,
sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
from #table1
group by calldate, project
set @.site = '2'
select calldate, project,
sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
from #table1
group by calldate, project
"ninel g via webservertalk.com" wrote:

>
I have a table TABLE1. My company has 2 sites. This table contains employe
es
>
with the amount of hours they worked on which project at which sites.
>
>

code:

>
 CREATE TABLE #TABLE1 (
>
 Calldate varchar(10) NULL,
>
 Employee varchar(10) NULL,
>
 Project varchar(10) NULL,
>
 Hours decimal(10,4) NULL,
>
 Site varchar(1) NULL)
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '123', 'EAUD5', 2.5, '2')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '246', 'EACQ5', 3, '2')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ5', 2, '1')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ6', 1.5, '1')
>
>
 INSERT #TABLE1 (calldate, employee, project, hours, site)
>
 VALUES ('20060217', '369', 'EACQ6', 5, '2')
>

>
>
I need to figure out the following:
>
I need the total hours of employees from both sites ONLY if they worked on
a
>
project that ended in a 5. If employees worked on projects that did not en
d
>
in 5 I need the totals for their site only. A parameter of site will be
>
passed to the stored procedure.
>
>
So for example: If site parameter of 1 is passed.
>
I need to see the following results:
>
>
Calldate Project TotalHours
>
20060217 EAUD5 2.5
>
20060217 EACQ5 5
>
20060217 EACQ6 1.5
>
>
If site parameter of 2 is passed.
>
I need to see the following results:
>
>
Calldate Project TotalHours
>
20060217 EAUD5 2.5
>
20060217 EACQ5 5
>
20060217 EACQ6 5
>
>
Any help would be greatly appreciated,
>
>
Thanks,
>
ninel
>
>
--
>
Message posted via http://www.webservertalk.com
>
|||Thnak you so much for teh quick response.
Mark Williams wrote:
>Thanks for posting DDL and sample data.
>declare @.site varchar(1)
>set @.site = '1'
>select calldate, project,
>sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
>from #table1
>group by calldate, project
>set @.site = '2'
>select calldate, project,
>sum(case when site = @.site or right(project,1) = '5' then hours else 0 end)
>from #table1
>group by calldate, project
>
>[quoted text clipped - 49 lines]
Message posted via http://www.webservertalk.comsql

Cant Export Fields w/ over 255 characters

I have a query where I am trying to export a column that has around
2000 characters and it is truncated. What is not setup in MS SQL
correctly so that DTS will export the csv correctly.

Thanks,
John<quinniii@.yahoo.com> wrote in message
news:1109013962.276779.70380@.o13g2000cwo.googlegro ups.com...
>I have a query where I am trying to export a column that has around
> 2000 characters and it is truncated. What is not setup in MS SQL
> correctly so that DTS will export the csv correctly.
> Thanks,
> John

This might be the issue:

http://www.sqldts.com/?297

If that doesn't help, then I suggest you post more information - what
version of MSSL, what data source and target types do you have, what sort of
task/transformation are you using to move the data etc.

Simon

cant drop

Why can't I drop the database? Is this query correct Drop database databasename?what's the error?

if you don't even know the syntax, I'm betting you can't drop it because you don't have the right permissions. :)

Can't debug SP from Query Analizer with SP3a

Hi,
I am able to debug them correctly from VS.NET 2003, but trying from Query
Analizer gives me this error:
Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied on
object 'sp_sdidebug', database 'master', owner 'dbo'.
Any ideas?
Thanks in advance,
Juan Dent, M.Sc.
The first thing to do is to make sure that the account you are using can
execute the stored procedure indicated in the error message:
GRANT EXECUTE ON sp_sdidebug TO <your user account goes here>
Keith
"Juan Dent" <juan5@.dev.com> wrote in message
news:058F454E-48E0-4194-BBA2-21A953BF2B53@.microsoft.com...
> Hi,
> I am able to debug them correctly from VS.NET 2003, but trying from Query
> Analizer gives me this error:
> Server: Msg 229, Level 14, State 5, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission denied
on
> object 'sp_sdidebug', database 'master', owner 'dbo'.
> Any ideas?
> --
> Thanks in advance,
> Juan Dent, M.Sc.
|||Yes I have done this, to no avail.
"Keith Kratochvil" wrote:

> The first thing to do is to make sure that the account you are using can
> execute the stored procedure indicated in the error message:
> GRANT EXECUTE ON sp_sdidebug TO <your user account goes here>
> --
> Keith
>
> "Juan Dent" <juan5@.dev.com> wrote in message
> news:058F454E-48E0-4194-BBA2-21A953BF2B53@.microsoft.com...
> on
>

can't create SP with a CTE

hi
i can't create a stored proc
with a CTE ( for recursion )
if i execute the query it works fine
but when i try to put it in a stored proc, it fails
with an error like
"Incorrect syntax near 'with'. If this is intended to be a common table
expression, you need to explicitly terminate the previous statement with a
semi-colon"
this is the query
--
with recursivo( _regla, _verbo, _pregla, _pverbo )
as (
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
where f.vf_regla= 199 and f.vf_verbo= 0
union all
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
inner join recursivo x on
x._regla= f.vf_regla and
x._verbo= f.vf_verbo
)
select *
from recursivo
atte,
Hernnsolved !
i have a SET NOCOUNT ON before the CTE
it should be SET NOCOUNT ON ;
( with a semicolon)
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:Ot6FoWsnGHA.3512@.TK2MSFTNGP02.phx.gbl...
| hi
| i can't create a stored proc
| with a CTE ( for recursion )
|
| if i execute the query it works fine
| but when i try to put it in a stored proc, it fails
| with an error like
| "Incorrect syntax near 'with'. If this is intended to be a common table
| expression, you need to explicitly terminate the previous statement with a
| semi-colon"
|
| this is the query
| --
|
| with recursivo( _regla, _verbo, _pregla, _pverbo )
| as (
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| where f.vf_regla= 199 and f.vf_verbo= 0
| union all
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| inner join recursivo x on
| x._regla= f.vf_regla and
| x._verbo= f.vf_verbo
| )
| select *
| from recursivo
|
| --
| atte,
| Hernn
|
|

can't create SP with a CTE

hi
i can't create a stored proc
with a CTE ( for recursion )
if i execute the query it works fine
but when i try to put it in a stored proc, it fails
with an error like
"Incorrect syntax near 'with'. If this is intended to be a common table
expression, you need to explicitly terminate the previous statement with a
semi-colon"
this is the query
--
with recursivo( _regla, _verbo, _pregla, _pverbo )
as (
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
where f.vf_regla= 199 and f.vf_verbo= 0
union all
select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
from VerboFlujo f
inner join recursivo x on
x._regla= f.vf_regla and
x._verbo= f.vf_verbo
)
select *
from recursivo
--
atte,
Hernánsolved !
i have a SET NOCOUNT ON before the CTE
it should be SET NOCOUNT ON ;
( with a semicolon)
--
atte,
Hernán
"bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
news:Ot6FoWsnGHA.3512@.TK2MSFTNGP02.phx.gbl...
| hi
| i can't create a stored proc
| with a CTE ( for recursion )
|
| if i execute the query it works fine
| but when i try to put it in a stored proc, it fails
| with an error like
| "Incorrect syntax near 'with'. If this is intended to be a common table
| expression, you need to explicitly terminate the previous statement with a
| semi-colon"
|
| this is the query
| --
|
| with recursivo( _regla, _verbo, _pregla, _pverbo )
| as (
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| where f.vf_regla= 199 and f.vf_verbo= 0
| union all
| select f.rg_codigo, f.vb_codigo, f.vf_regla, f.vf_verbo
| from VerboFlujo f
| inner join recursivo x on
| x._regla= f.vf_regla and
| x._verbo= f.vf_verbo
| )
| select *
| from recursivo
|
| --
| atte,
| Hernán
|
|

Can't create linked server

Dear Sir,
I have a remote db server which I can connect using Query Analyzer but can't
setup a linked server to it, using same user account, any suggestion how to
trouble shoot?
Henry
Henry
What is an error you are getting?
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> Dear Sir,
> I have a remote db server which I can connect using Query Analyzer but
> can't
> setup a linked server to it, using same user account, any suggestion how
> to
> trouble shoot?
> Henry
|||Dear Uri Dimant,
I can create the linked server but whenever I tried to access, e.g. click on
the Security->Linked Server->RemoteServer->Table/View, it return the error of
Error 7399
Both of my local and remote db are SQL 2000.
Henry
"Uri Dimant" wrote:

> Henry
> What is an error you are getting?
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
>
>
|||Hi Henry,
Do u have sa password on both the server.
if yes then drop ur linked server and create another one
give remote user and local user as sa and sa password.
in the other server type should be sql server if u create linked
server between sql server.
click the remote login option and give sa and sa password.
check the server option
these three should be selected
dataaccess,
RPC and RPC Out.
hope this help u
from
Doller
Henry wrote:[vbcol=seagreen]
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click on
> the Security->Linked Server->RemoteServer->Table/View, it return the error of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
|||This has some example syntax for adding a linked server:
http://msdn.microsoft.com/library/en..._adda_8gqa.asp
-oj
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:5B480C91-E86A-46FC-AFC3-D59D8CBBBCCA@.microsoft.com...[vbcol=seagreen]
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click
> on
> the Security->Linked Server->RemoteServer->Table/View, it return the error
> of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
|||Dear Doller,
I don't have SA account on remote db right now, but I have other linked
server using account without SA rights, e.g. have dbo rights and can also
make connection. Do you know if there any docuement for the access rights
requirement?
Henry
"doller" wrote:

> Hi Henry,
> Do u have sa password on both the server.
> if yes then drop ur linked server and create another one
> give remote user and local user as sa and sa password.
> in the other server type should be sql server if u create linked
> server between sql server.
> click the remote login option and give sa and sa password.
> check the server option
> these three should be selected
> dataaccess,
> RPC and RPC Out.
> hope this help u
> from
> Doller
>
> Henry wrote:
>

Can't create linked server

Dear Sir,
I have a remote db server which I can connect using Query Analyzer but can't
setup a linked server to it, using same user account, any suggestion how to
trouble shoot?
HenryHenry
What is an error you are getting?
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> Dear Sir,
> I have a remote db server which I can connect using Query Analyzer but
> can't
> setup a linked server to it, using same user account, any suggestion how
> to
> trouble shoot?
> Henry|||Dear Uri Dimant,
I can create the linked server but whenever I tried to access, e.g. click on
the Security->Linked Server->RemoteServer->Table/View, it return the error of
Error 7399
Both of my local and remote db are SQL 2000.
Henry
"Uri Dimant" wrote:
> Henry
> What is an error you are getting?
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> > Dear Sir,
> > I have a remote db server which I can connect using Query Analyzer but
> > can't
> > setup a linked server to it, using same user account, any suggestion how
> > to
> > trouble shoot?
> > Henry
>
>|||Hi Henry,
Do u have sa password on both the server.
if yes then drop ur linked server and create another one
give remote user and local user as sa and sa password.
in the other server type should be sql server if u create linked
server between sql server.
click the remote login option and give sa and sa password.
check the server option
these three should be selected
dataaccess,
RPC and RPC Out.
hope this help u
from
Doller
Henry wrote:
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click on
> the Security->Linked Server->RemoteServer->Table/View, it return the error of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
> > Henry
> > What is an error you are getting?
> >
> > "Henry" <Henry@.discussions.microsoft.com> wrote in message
> > news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> > > Dear Sir,
> > > I have a remote db server which I can connect using Query Analyzer but
> > > can't
> > > setup a linked server to it, using same user account, any suggestion how
> > > to
> > > trouble shoot?
> > > Henry
> >
> >
> >|||This has some example syntax for adding a linked server:
http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
-oj
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:5B480C91-E86A-46FC-AFC3-D59D8CBBBCCA@.microsoft.com...
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click
> on
> the Security->Linked Server->RemoteServer->Table/View, it return the error
> of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
>> Henry
>> What is an error you are getting?
>> "Henry" <Henry@.discussions.microsoft.com> wrote in message
>> news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
>> > Dear Sir,
>> > I have a remote db server which I can connect using Query Analyzer but
>> > can't
>> > setup a linked server to it, using same user account, any suggestion
>> > how
>> > to
>> > trouble shoot?
>> > Henry
>>|||Dear Doller,
I don't have SA account on remote db right now, but I have other linked
server using account without SA rights, e.g. have dbo rights and can also
make connection. Do you know if there any docuement for the access rights
requirement?
Henry
"doller" wrote:
> Hi Henry,
> Do u have sa password on both the server.
> if yes then drop ur linked server and create another one
> give remote user and local user as sa and sa password.
> in the other server type should be sql server if u create linked
> server between sql server.
> click the remote login option and give sa and sa password.
> check the server option
> these three should be selected
> dataaccess,
> RPC and RPC Out.
> hope this help u
> from
> Doller
>
> Henry wrote:
> > Dear Uri Dimant,
> > I can create the linked server but whenever I tried to access, e.g. click on
> > the Security->Linked Server->RemoteServer->Table/View, it return the error of
> > Error 7399
> >
> > Both of my local and remote db are SQL 2000.
> >
> > Henry
> >
> > "Uri Dimant" wrote:
> >
> > > Henry
> > > What is an error you are getting?
> > >
> > > "Henry" <Henry@.discussions.microsoft.com> wrote in message
> > > news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> > > > Dear Sir,
> > > > I have a remote db server which I can connect using Query Analyzer but
> > > > can't
> > > > setup a linked server to it, using same user account, any suggestion how
> > > > to
> > > > trouble shoot?
> > > > Henry
> > >
> > >
> > >
>

Can't create linked server

Dear Sir,
I have a remote db server which I can connect using Query Analyzer but can't
setup a linked server to it, using same user account, any suggestion how to
trouble shoot?
HenryHenry
What is an error you are getting?
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
> Dear Sir,
> I have a remote db server which I can connect using Query Analyzer but
> can't
> setup a linked server to it, using same user account, any suggestion how
> to
> trouble shoot?
> Henry|||Dear Uri Dimant,
I can create the linked server but whenever I tried to access, e.g. click on
the Security->Linked Server->RemoteServer->Table/View, it return the error o
f
Error 7399
Both of my local and remote db are SQL 2000.
Henry
"Uri Dimant" wrote:

> Henry
> What is an error you are getting?
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:C31AA693-9574-4636-B56E-5E004189F6CF@.microsoft.com...
>
>|||Hi Henry,
Do u have sa password on both the server.
if yes then drop ur linked server and create another one
give remote user and local user as sa and sa password.
in the other server type should be sql server if u create linked
server between sql server.
click the remote login option and give sa and sa password.
check the server option
these three should be selected
dataaccess,
RPC and RPC Out.
hope this help u
from
Doller
Henry wrote:[vbcol=seagreen]
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click
on
> the Security->Linked Server->RemoteServer->Table/View, it return the error
of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
>|||This has some example syntax for adding a linked server:
http://msdn.microsoft.com/library/e...p_adda_8gqa.asp
-oj
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:5B480C91-E86A-46FC-AFC3-D59D8CBBBCCA@.microsoft.com...[vbcol=seagreen]
> Dear Uri Dimant,
> I can create the linked server but whenever I tried to access, e.g. click
> on
> the Security->Linked Server->RemoteServer->Table/View, it return the error
> of
> Error 7399
> Both of my local and remote db are SQL 2000.
> Henry
> "Uri Dimant" wrote:
>|||Dear Doller,
I don't have SA account on remote db right now, but I have other linked
server using account without SA rights, e.g. have dbo rights and can also
make connection. Do you know if there any docuement for the access rights
requirement?
Henry
"doller" wrote:

> Hi Henry,
> Do u have sa password on both the server.
> if yes then drop ur linked server and create another one
> give remote user and local user as sa and sa password.
> in the other server type should be sql server if u create linked
> server between sql server.
> click the remote login option and give sa and sa password.
> check the server option
> these three should be selected
> dataaccess,
> RPC and RPC Out.
> hope this help u
> from
> Doller
>
> Henry wrote:
>

Can't connet to MSDE with Query Analyzer

I'm running MSDE on a WIN2K and I'm having a problem accessing the MSDE from
a laptop with WINXP Professional. I'm using a NETGEAR MR314 cable router w/
firewall to connect the two computers and I'm getting the following error:
Unable to connec to server SERVERNAME
Server: Msg 17, Level 16, State1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied
I've tried by creating a new account with owner rights to all databases and
I still get the same error when connecting with Query Analyzer. When I get
the "Connect to SQL Server" dialog box I can see the server but when I enter
the user name an password it gives me the error message.
The client is attempting to connect using TCP/IP so you will
want to make sure this protocol is enabled for MSDE. You can
use the server network utility to check, enable the
protocols. The utility file name is svrnetcn.exe. You can
find it in the Tools\Binn directory of your installation
path.
You can also find some general troubleshooting for the error
at:
INF: Potential Causes of the "SQL Server Does Not Exist or
Access Denied" Error Message
http://support.microsoft.com/?id=328306
-Sue
On Sun, 2 May 2004 16:00:49 -0500, "Sal Young"
<salyoung@.comcast.net> wrote:

>I'm running MSDE on a WIN2K and I'm having a problem accessing the MSDE from
>a laptop with WINXP Professional. I'm using a NETGEAR MR314 cable router w/
>firewall to connect the two computers and I'm getting the following error:
>Unable to connec to server SERVERNAME
>Server: Msg 17, Level 16, State1
>[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
>access denied
>
>I've tried by creating a new account with owner rights to all databases and
>I still get the same error when connecting with Query Analyzer. When I get
>the "Connect to SQL Server" dialog box I can see the server but when I enter
>the user name an password it gives me the error message.
>

Can't connet to MSDE with Query Analyzer

I'm running MSDE on a WIN2K and I'm having a problem accessing the MSDE from
a laptop with WINXP Professional. I'm using a NETGEAR MR314 cable router w/
firewall to connect the two computers and I'm getting the following error:
Unable to connec to server SERVERNAME
Server: Msg 17, Level 16, State1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not
exist or
access denied
I've tried by creating a new account with owner rights to all databases and
I still get the same error when connecting with Query Analyzer. When I get
the "Connect to SQL Server" dialog box I can see the server but when I enter
the user name an password it gives me the error message.Hi Sal,
Try creating a Test ODBC DSN to the MSDE server. Then review the OS
Error returned.
This will help troubleshoot the problem.
Example:
10060 = Connection Timeout
11001 = Host not found.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Can't connet to MSDE with Query Analyzer

I'm running MSDE on a WIN2K and I'm having a problem accessing the MSDE from
a laptop with WINXP Professional. I'm using a NETGEAR MR314 cable router w/
firewall to connect the two computers and I'm getting the following error:
Unable to connec to server SERVERNAME
Server: Msg 17, Level 16, State1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied
I've tried by creating a new account with owner rights to all databases and
I still get the same error when connecting with Query Analyzer. When I get
the "Connect to SQL Server" dialog box I can see the server but when I enter
the user name an password it gives me the error message.
MSDE doesn't listen to any netlibs by default. You can enable netlibs using the svrnetcn.exe utility.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sal Young" <salyoung@.comcast.net> wrote in message news:MMadnZ23tfPw_AjdRVn-sA@.comcast.com...
> I'm running MSDE on a WIN2K and I'm having a problem accessing the MSDE from
> a laptop with WINXP Professional. I'm using a NETGEAR MR314 cable router w/
> firewall to connect the two computers and I'm getting the following error:
> Unable to connec to server SERVERNAME
> Server: Msg 17, Level 16, State1
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied
>
> I've tried by creating a new account with owner rights to all databases and
> I still get the same error when connecting with Query Analyzer. When I get
> the "Connect to SQL Server" dialog box I can see the server but when I enter
> the user name an password it gives me the error message.
>