OpenQuery not working after applyin SQL Server SP4

SELECT * FROM openquery( OLAP_PLS,'SELECT { [Measures].[Produced Qty] } ON COLUMNS , { [Time].[Year].&[2007].&[1].&[2] } ON ROWS FROM [Employee]')

Issue:

We have Linked Server setup to OLAP Server. which was working fine for long time. After applying SP4 on SQL Server, we started getting this error. But the same query runs from another server with SP3.

After applying SP4, I have also applied 2 hotfixes to SQL Server. One hot fix is related to using using memory more than 3GB and another hotfix is for this Linked server issue. but still I have the problem.

SELECT * FROM openquery( OLAP_PLS,'SELECT { [Measures].[Produced Qty] } ON COLUMNS , { [Time].[Year].&[2007].&[1].&[2] } ON ROWS FROM [Employee]')

Error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSOLAP' reported an error.
[OLE/DB provider returned message: Database 'PLS' does not exist.]
OLE DB error trace [OLE/DB Provider 'MSOLAP' IDBInitialize::Initialize returned 0x80004005: ].

Appreciate your help.

Thanks,

Varad

sql

openquery datasource not working

Hi,
I hope someone can help with a query that's puzzling me.
I have this query that I can't understand.
SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
'SELECT createdate,loginname FROM Master.dbo.syslogins where
isntname=1'
) AS p
When I run it, it returns data, but not from the server I specified in
the Datasource. In fact I can change the Datasource to anything even
gibberish and it still returns the same set of records.On Sep 18, 4:55 pm, Bombastic <mbale...@.hotmail.com> wrote:
> Hi,
> I hope someone can help with a query that's puzzling me.
> I have this query that I can't understand.
> SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
> Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
> 'SELECT createdate,loginname FROM Master.dbo.syslogins where
> isntname=1'
> ) AS p
> When I run it, it returns data, but not from the server I specified in
> the Datasource. In fact I can change the Datasource to anything even
> gibberish and it still returns the same set of records.
The connection string that you are using is wrong. Instead of
datasource=server1 it should be server=server1.
Adi|||The argument is called "Data Source" not "datasource".
ML
--
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/|||On Sep 18, 4:25 pm, Adi <adic...@.hotmail.com> wrote:
> On Sep 18, 4:55 pm, Bombastic <mbale...@.hotmail.com> wrote:
> > Hi,
> > I hope someone can help with a query that's puzzling me.
> > I have this query that I can't understand.
> > SELECT p.* FROM OPENROWSET('SQLOLEDB' , 'Trusted_Connection=yes;
> > Integrated Security=SSPI;Datasource=server1;Initial_Catalog=Master;',
> > 'SELECT createdate,loginname FROM Master.dbo.syslogins where
> > isntname=1'
> > ) AS p
> > When I run it, it returns data, but not from the server I specified in
> > the Datasource. In fact I can change the Datasource to anything even
> > gibberish and it still returns the same set of records.
> The connection string that you are using is wrong. Instead of
> datasource=server1 it should be server=server1.
> Adi
Thanks, that did the trick.I can't think where I got the Datasource
from.|||On Sep 18, 4:30 pm, ML <M...@.discussions.microsoft.com> wrote:
> The argument is called "Data Source" not "datasource".
> ML
> --
> Matija Lah, SQL Server MVPhttp://milambda.blogspot.com/
Thanks for your response. I did try Data Source but it did the same
thing. Problem resolved with Adi's response but thanks anyway.

Opening Existing RDL in Visual Studio

This HAS to be the most common question - yet I have not found it.
I have TWO developers.
One is working on reports, now the other wants to help.
How can Visual Studio open the RDL files already on the server?
It's a "team" question really. How do I do it?
Thank in advance, JerryThis behavior is not support by SQL Server 2000 Reporting Services. You
would either have to share the copy of the report already on disk or
download a copy from the server and edit that one. This feature request is
currently on our wishlist.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410120746.c9f3216@.posting.google.com...
> This HAS to be the most common question - yet I have not found it.
> I have TWO developers.
> One is working on reports, now the other wants to help.
> How can Visual Studio open the RDL files already on the server?
> It's a "team" question really. How do I do it?
> Thank in advance, Jerry|||Thank you.
Hearing "not supported" saves me lots of time in research.
Best regards, Jerry

Open-ended querying of DB (??)

I am working on a project with a couple of hundred tables and views. There is one primary table around which all other tables revolve with the primary table having somewhere around 42 to 45 million records. Some of the related tables can have many times this number of records. The data is static in that it only changes at specific intervals (once a month or so) when more records are added and some "old" records are removed.

My problem is that users need to query these tables without any major restrictions. I mean, the front end will allow users to create almost any selection criteria form the many columns in the DB. The selection of values will be controlled via drop-downs and lists from where the user will select the criteria. The other consideration is that the grouping can be for any value and up to ten levels deep. Our users are not sophisticated enough to create their own SQL-like queries so we have to guide them through the process of selecting the values…

I am trying to create stored procedures that will allow this behavior but I continue to run into the problem of how to properly join the tables when the combinations are almost endless. My conclusion is that although it seems this seems to be a case where D-SQL is a good choice, there are some draw-backs I am not sure we can afford, specifically the security issues. Furthermore, the creation of a D-SQL "code-generator" seems like almost impossible task and time is of the essence.

Has anyone here had any experience with a similar situation that can shed some light?I think without dynamic SQL, you're stuck with creating a view for every possible combination of joins - not really an option. I think you'll need to use it. Is it possible to provide the highest priority querying and slowly add more tables as you have a chance to build it? If a user isn't sophisticated enough to build SQL, will they be aware enough to even understand what joining tables does for them?

I've done 'build-your-own' query-generating stuff before, but never to that scope. That could be a tough one.|||A Friend of mine was reading an article in visualstudiomagazine.com that might be of some assistance to you. Check out the online version http://www.fawcette.com/vsm/2004_10/magazine/columns/databasedesign/

Hth,|||Scott, thanks for the heads-up. I will post my impressions on the article later on.|||PD, I have no choice but to look for a solution. Our users currently have a system that is based on a "flat-file" format where creating the queries to produce the reports is fairly easy. When we decided to re-write the system as a web based application, the decision was made to move to SQL Server and take advantage of its power. We normalized the files, added records for a wider time frame and have created a beautiful data structure. Unfortunately now we are confronted with the issues of querying the data in a multitude of ways.

We are considering de-normalizing the data a little, using Analysis Services, English Queries, and others. Given the confidential nature of the data, security is a must and using D-SQL may be a problem. We can live with a system where the user makes his selection from lists and drop-downs and we construct the query behind the scenes; we do not expect them to know SQL.|||Beleive it or not there are actual tools out there that exist to perform this task. Mind you some may seem expensive... but they work, and the amount of development time saved to get something that works and may be useful in other areas in your organization may warrant the costs.

The tools fall into the Business Intelligence area of software, and are typically called EII or something to that nature. EII stands for Enterprise Information Integration.

Another way to explain it, you need a tool, typically made to fetch data from multiple sources, apply english names to them, then you specify how the individual "views of data" could be related to another. Next the tool gives you a GUI interface and/or exposes an API allowing you to say "I want data from 'a' and 'b' filtered on 'c' and grouped on 'd'."

Nimble Technologies made such a tool. My company, Actuate Corporation, has acquired them to use their technology within our own software. I'm not sure if we did an outright takeover, or just bought largely into their company or what, but Nimble may or may not exist. In either case, they had competitors, so the tools do exists out there.

I would suggest looking up Business Intelligence tools, or publications that frequently discuss such software. Publications geared towards CIOs and IT management would be another area to look.

Open and close SQL server via command prompt

I need command line to open and close SQL server
I'am working with a mirroring batch file, It performs a mirroring of a works
tation (database files) in a lab and a secured backuped server
Someone can help me
thanksI assume you mean start and stop the SQL Server service?
NET START MSSQLSERVER
NET STOP MSSQLSERVER
Don't forget to handle the SQL Server agent server, which is dependent on th
e SQL Server service. Also, if it
is a named instance, you need to adjust the service name (see the "Services"
windows applet).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Capone Raphael" <capone.raphael@.skynet.be> wrote in message
news:5409F787-DF3E-4B4F-A6FD-950B5AFA7268@.microsoft.com...
> I need command line to open and close SQL server
> I'am working with a mirroring batch file, It performs a mirroring of a workstation
(database files) in a lab
and a secured backuped server
> Someone can help me
> thanks|||I am not sure what you mean by "open and close." =20
If you need to connect via the command line, try osql.exe
--=20
Keith
"Capone Raphael" <capone.raphael@.skynet.be> wrote in message =
news:5409F787-DF3E-4B4F-A6FD-950B5AFA7268@.microsoft.com...
> I need command line to open and close SQL server=20
> I'am working with a mirroring batch file, It performs a mirroring of a =
workstation (database files) in a lab and a secured backuped server
>=20
> Someone can help me=20
>=20
> thanks

Only the administrator can open the reports

Hi,
I've been struggling with this problem for a couple of days. The RS system
seems to be working fine. However only the administrators can open the
reports. If a user attempts to open a report, a login window pops up.
After pressing OK three times, a message "You are not authorized to view
this page" is displayed. Any ideas on how to fix this? Thanks.
Fabian CevallosMake sure you have given the users permissions to view the report (Browser is
the default access level).
SQLRS Server requires you to authenticate users of a domain. Those admins
that have full permissions on the server itself will have no problems, but
until you 'activate' accounts, all other domain accounts are unknow - and
therefore unauthorized - to access any reports at all.
Now comes the crunch. SQLRS will allow you to create access for domain
accounts. Unfortunately I have not -as yet - found a way to create local
group acconus per se. Therefore, if you want a set number of managers to view
a report, you may need to either set the group up on the domain or manage
them individually on the SQLRS server.
"Fabian Cevallos" wrote:
> Hi,
>
> I've been struggling with this problem for a couple of days. The RS system
> seems to be working fine. However only the administrators can open the
> reports. If a user attempts to open a report, a login window pops up.
> After pressing OK three times, a message "You are not authorized to view
> this page" is displayed. Any ideas on how to fix this? Thanks.
>
> Fabian Cevallos
>
>
>|||The users had permissions to view the report... I ended up removing SQLRS
and installing it again. Now everything works!
"Logicalman" <Logicalman@.discussions.microsoft.com> wrote in message
news:20743E32-2494-4FDE-9ED7-0A34A3A94FD3@.microsoft.com...
> Make sure you have given the users permissions to view the report (Browser
> is
> the default access level).
> SQLRS Server requires you to authenticate users of a domain. Those admins
> that have full permissions on the server itself will have no problems, but
> until you 'activate' accounts, all other domain accounts are unknow - and
> therefore unauthorized - to access any reports at all.
> Now comes the crunch. SQLRS will allow you to create access for domain
> accounts. Unfortunately I have not -as yet - found a way to create local
> group acconus per se. Therefore, if you want a set number of managers to
> view
> a report, you may need to either set the group up on the domain or manage
> them individually on the SQLRS server.
> "Fabian Cevallos" wrote:
>> Hi,
>>
>> I've been struggling with this problem for a couple of days. The RS
>> system
>> seems to be working fine. However only the administrators can open the
>> reports. If a user attempts to open a report, a login window pops up.
>> After pressing OK three times, a message "You are not authorized to view
>> this page" is displayed. Any ideas on how to fix this? Thanks.
>>
>> Fabian Cevallos
>>
>>

Only for SQL GURUS - Calling external procedures from Functions

I am using SQL Server 2005 Developer Edition. I am working on a conversion project from Oracle to SQL Server 2005.

As part of this, in order to replicate Oracle sequence functionality in SQL Server, the design called for writing a function that would accept a parameter , say sequence name. There will be a user table, say, SEQUENCE_GENERATOR with the following structure.

Column Name Data Type Nullability
-- -
Sequence_Name varchar(50) N
Last_Number int N

Everytime before generating the next sequence this function would be called. It would do the following : -

-
Select @.Last_Number = Last_Number
from SEQUENCE_GENERATOR WITH (UPDLOCK)
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>

SET @.Last_Number = @.Last_Number +1

UPDATE SEQUENCE_GENERATOR
SET Last_Number = @.Last_Number
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>

RETURN @.Last_Number
Since we are fetching the value of Last_Number(by calling the function) before inserting into our tables, we do not want users simultaneously accessing the same sequence to end up with PK violations and hence the use of UPDLOCK hint.

Now the real problem: -

Since the above three steps are a DML operation excepting the RETURN clause, we cannot do it in a function. However we can call an external stored procedure that does the above UML operations to accept the parameter and then return an output value. This output value would then be returned by the function.

Now I would like to know if the above approach is possible with external sps as I have no prior experience in this. Also, can someone point important resources where I can code the above thing in C++...

Thanks & Regards
Imtiaz

Before I get to the answer...

1. In practice sequence tables are a huge headache in any database design, I find they're more common in the Oracle world than any other, especially in large database designs that required a federated/distributed/replication model. If you can avoid a sequence table, consider using uniqueidentifier columns for PKs instead, they will remove a lot of headache (read: labor hours) and simplify software design at all levels.

2. Locking (table or otherwise) just creates artificial contention, if 1000 clients all decided to create a record at the same time they'd all have to stand in line. *owe*

3. In your sequence table you might consider using a seeded identity column for the PK, and leave the 'sequence name' as a regular data value. You could then write a UDF (function) in sql server that simply inserted the sequence name into the sequence table, and upon completion you acquire the generated number via @.@.IDENTITY (look this up in Books Online if you're not familiar with it already). This will help avoid the locking (which is hell on performance) and still get everything contained within sql server (no need to go the xp route). This is an option so long as the rest of the solution doesn't rely on a contigious set of numbers for a given sequence name (table?).
To answer your question, though, an extended stored procedure (xp/xsp) is implemented as a standard C dll, so you could implement it in C++ so long as you had cdecl exports sql server could consume.

Also, the registration process is tacky (look up sp_addextendedproc) since it requires access to the master table, but yes you can do what you want using an xp and a little elbow grease.

See: Creating an Extended Stored Procedure (MSDN)

Hope that helps,
Shaun

|||The logic that you have below is not quite correct unless you enclose the statements in a transaction. Without a transaction, the UPDLOCK will be released after the SELECT statement completes.

Select @.Last_Number = Last_Number
from SEQUENCE_GENERATOR WITH (UPDLOCK)
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>

SET @.Last_Number = @.Last_Number +1

UPDATE SEQUENCE_GENERATOR
SET Last_Number = @.Last_Number
WHERE Sequence_Name = <func parameter passed i.e. Sequence Name>

RETURN @.Last_Number

A more concise way of writing this in SQL Server is to use the UPDATE statement extension like:

UPDATE SEQUENCE_GENERATOR
SET @.Last_Number = Last_Number = Last_Number + 1
WHERE Sequence_Name = <sequence name>

Having said this, it is still not possible to perform data modifying operations from within UDFs. You can write an extended stored procedure. But that is more trouble actually. It can easily lead to concurrency issues, poor performance etc. You will also have to use bound connections. You could still go this route but be aware of the risks especially in a OLTP environment. You can use Visutal Studio templates to create the stubs required for implementing extended stored procedures. In any case, I would recommend against converting Oracle code verbatim. Identity provides a much faster and efficient mechanism to generate sequence numbers. If you need the sequence mechanism then why not implement it as a stored procedure and call it from within the proc requiring it. This is much more scalaeble way to implement it in TSQL.|||Shaun and Uma

The solutions you have given me instead of using external stored procedures I am aware of that. The application would run on Oracle or SQL Server backedn and the idea is to have most of the things as close as possible. I know this can be achieved by SPs but we desperately need functions to do that.

Uma
The reason we have UPDLOCK hint is for concurrency issues.

Now how can we write T-SQL code in C++ is my question. where do I start with?|||I am still waiting for some inputs on this...The problem is how do I start a xaction, access the database in extended procedure.

Regards
Imtiaz|||

Shaun Wilson wrote:

See: Creating an Extended Stored Procedure (MSDN)

Also

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_22sz.asp

and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odssql/ods_6_con_01_22sz.asp

Sync the toc on their website, all the docs you need are there. If all else fails you might consider using MSDTC as a transaction coordinator between the 'client', your xp and sql server.

Only enable the string truncation prevention of ANSI_WARNINGS

I'm working with some long standing VB/SQL Server applications and for
the second time we've suffered from having the parameters to a stored
procedure call get silently truncated now that the data field has got
much larger than when the code was developed all those years ago. This
is always very hard to debug and I'd really like SQL Server to throw
an error when this happens.

I don't feel confident enablying the full ANSI_WARNINGS as it is
likely to affect lots of functionality in the database in
unanticipated ways.

What I'd like to be able to do is enable only the ANSI check for the
string data getting truncated but haven't been able to find a way to
do this. Is it possible?

Cheers
Dave"David Sharp" <dave@.daveandcaz.freeserve.co.uk> wrote in message
news:ca434844.0401080924.40dd7da1@.posting.google.c om...
> I'm working with some long standing VB/SQL Server applications and for
> the second time we've suffered from having the parameters to a stored
> procedure call get silently truncated now that the data field has got
> much larger than when the code was developed all those years ago. This
> is always very hard to debug and I'd really like SQL Server to throw
> an error when this happens.
> I don't feel confident enablying the full ANSI_WARNINGS as it is
> likely to affect lots of functionality in the database in
> unanticipated ways.
> What I'd like to be able to do is enable only the ANSI check for the
> string data getting truncated but haven't been able to find a way to
> do this. Is it possible?
> Cheers
> Dave

There's no 'subset' of ANSI_WARNINGS which will only raise an error on
string truncation, so your best bet is to fix your application, either by
making the stored proc parameter longer, or by validating the input in the
front end.

If neither of those are possible, then there aren't many options left,
except perhaps to raise an error if the parameter value is the same size as
the maximum possible size of the parameter data type. So if you have
char(20), assume that only values up to 19 characters are valid. But it
would be much better to fix the problem at the source, and validate the
input.

Simon|||David Sharp (dave@.daveandcaz.freeserve.co.uk) writes:
> I'm working with some long standing VB/SQL Server applications and for
> the second time we've suffered from having the parameters to a stored
> procedure call get silently truncated now that the data field has got
> much larger than when the code was developed all those years ago. This
> is always very hard to debug and I'd really like SQL Server to throw
> an error when this happens.
> I don't feel confident enablying the full ANSI_WARNINGS as it is
> likely to affect lots of functionality in the database in
> unanticipated ways.
> What I'd like to be able to do is enable only the ANSI check for the
> string data getting truncated but haven't been able to find a way to
> do this. Is it possible?

To add to what Simon said, you would probably have any use for ANSI_WARNINGS
anyway. ANSI_WARNINGS produces an error if you try to assign a column
a value which is too long. However, variable and parameter assignment
still truncates silently, even with ANSI_WARNINGS ON.

I would however encourage you to switch to ANSI_WARNINGS for other reasons.
This setting is required is some contexts, more precisely in distributed
queries and when you used indexed views and indexed computed columns.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946B48FD1863Yazorman@.127.0.0.1>...
> I would however encourage you to switch to ANSI_WARNINGS for other reasons.
> This setting is required is some contexts, more precisely in distributed
> queries and when you used indexed views and indexed computed columns.

Thanks for your help. Sounds like there's no short cut to identify
when this happens. We'll have to physically go through and make sure
it is checked for in each sproc.

Cheers
Dave

Online SQL Server resources?

I'm working on some research on online resources for SQL Server programming
and design. If you'd be willing to help, please follow the link below and
take the survey -- it's only 4 questions!
http://www.surveymonkey.com/s.asp?u=98075334236
Many thanks,
John"John Moody" <john@.wishtracker.com> wrote in message
news:e2qx0lpvDHA.2248@.TK2MSFTNGP09.phx.gbl...
> I'm working on some research on online resources for SQL Server
programming
> and design. If you'd be willing to help, please follow the link below and
> take the survey -- it's only 4 questions!
> http://www.surveymonkey.com/s.asp?u=98075334236
>
which asks
"How much would you be willing to pay for an online service that helped with
SQL Server database problems (design, programming, query construction) if
the service came with a 100% money-back guarantee?"
I believe microsoft.public.sqlserver.server already comes with a money-back
guarantee.
User ^
Expertise |
|
|
|
|
Problem Difficulty
David|||"John Moody" <john@.wishtracker.com> wrote in message
news:e2qx0lpvDHA.2248@.TK2MSFTNGP09.phx.gbl...
> I'm working on some research on online resources for SQL Server
programming
> and design. If you'd be willing to help, please follow the link below and
> take the survey -- it's only 4 questions!
> http://www.surveymonkey.com/s.asp?u=98075334236
> Many thanks,
> John
>
oops.
Meant to say that I once a problem is hard enough to merit a per-incident
fee, it may already be too hard so solve online.
David

Online book links not working

I am trying to use sql server books online to get sql
help. However some links are not working.
Error message states:
Object does'nt support this method or property
Reinstalled the books. But still not working.Can you provide some more details about which links are not working? When
do you get the error message exactly? Can you display any topics at all?
Gail Erickson [MSFT]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"sselva" <anonymous@.discussions.microsoft.com> wrote in message
news:026201c394e6$aee04c70$a401280a@.phx.gbl...
> I am trying to use sql server books online to get sql
> help. However some links are not working.
> Error message states:
> Object does'nt support this method or property
> Reinstalled the books. But still not working.
>