Tuesday, March 20, 2012
Another way than using a Cursor
I have the following example Table & Data...
Create Table Test
(
Cust_Code varchar(10),
Acc_Deal_Type Varchar(3),
Account_No Varchar(30),
AccountTypes Varchar(50)
)
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','MMC','12345678')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','MMC','12345679')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123456','CCL','12345680')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123457','MMC','12345681')
Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No)
Values ('123457','MMC','12345682')
I need to concatanate the distinct values of Acc_Deal_Types Column for
each Cust_Code and set the AccountTypes column with the Result.
So, in this example, the grouped results would be two rows with the
Cust_Code & AccountTypes Column selected:
1st Row
Cust_Code = 123456
AccountType = MMC,CCL
2nd Row
Cust_Code = 123457
AccountType = MMC
How can I create the AccountTypes column without using a cursor? The
Table in question has around 7000 rows.
Thanks
Barrytry
select distinct Acc_Deal_Type + Cust_Code as NewType into Test# from Test
update Test
set Acc_Deal_Type = NewType
-- select Cust_Code, Acc_Deal_Type, Account_No, AccountTypes , NewType
from Test a, Test# b
where NewType = Acc_Deal_Type + Cust_Code
increase the zize of the Acc_Deal_Type though|||Hi, Barry
See: http://www.aspfaq.com/show.asp?id=2529
Razvan
Monday, March 19, 2012
Another Stored Procedure Question...
Is it possible whithin a Stored Procedure send the table name as a parameter?
And some Columns to?
Ex.:
CREATE Procedure Xpto
@.TableName as ?
@.ColumnName as ?
@.SomeValue as nvarchar(10)
AS
SELECT * FROM @.TableName WHERE @.ColumnName = @.SomeValue
Thanks
JPP
create procedure SelectFrom @.table sysname, @.column sysname, @.value sysname
as
exec ('select * from '+ @.table + ' where ' +@.column + ' = ''' + @.value + '''')
You should protect the code above against SQL injection attacks like below:
declare @.tablename nvarchar(130), @.columnname nvarchar(130), @.sql nvarchar(4000)
set @.tablename = quotename(@.table)
set @.columnname = quotename(@.column)
set @.sql = 'select * from ' + @.tablename + ' where ' + @.columnname + ' = @.value'
exec sp_executesql @.sql, N'@.value nvarchar(4000)', @.value = @.value
However, there shouldn't be a need to write such generic stored procedures. It is not a good thing to do. You have to grant permissions to users since dynamic SQL is evaluated at run-time. So please write a stored procedure per table/feature/module that can handle the data access for you. Alternatively, you can also create views and expose the data.
|||Hi,When I post this question I was thinking on using the solution to dynamicly update some tables with the Numers of the documents.
Ex.:
Table Numbers
NroInvoice
NroReceipt
....
Then, to update one of the numbers I dont have to write a stored procedure for each on.
Do you think this is a bad aprotch?
Thank you for your time.
JPP|||Another reason to avoid such generic stored procedures is that they cannot be optimized and do not generate a pre-compiled execution plan that would be kept for repeated execution.|||Hello...
I would not use a table to hold values like this...
Those information is already in the DB and there is no need to denormalize it. If there are propper indexes on those fields you want to evaluate you can wrtite a small view that extracts the data you need...
By the way... Why is there no SQL Code button on the form ;)
create view DocCount
as
select (select count(*) from Documents where type = 'Invoice') NrOfInvoices,
(select count(*) from Documents where type = 'Mail') NrOfMails
or another way...
create view DocCount2
as
select type, count(*) from Documents group by type
This way there is no need to update this table
Another SQL Stored Procedure Question
I need to create a table in the Access97 database and then populate it with SQL Server2k data. I'm using a SELECT ... INTO statement (which works fine in the SQL Enterprise Manager QRY thingy (I'm soooo technical) but it's not working when the stored procedure is called. The Access97 database is set up as a linked server, I'm pulling the database name in thru a parameter along with the condition. Maybe one of you can see something I don't with this code?
CREATE PROCEDURE dbo.usp_PutData @.DbName nvarchar(20), @.State nvarchar(4), @.AirType nvarchar(10)
AS
declare @.QueryIs as varchar(8000)
select @.QueryIs = Case upper(@.AirType)
when 'NFDC'then "SELECT (FldName1, FldName2, FldName3, FldName4, FldName5, FldName6, FldName7) INTO " + @.DbName + "...Facility FROM tbl_NFDC_Facility WHERE (Assoc_State = '" + @.State + "')"
end
Select @.QueryIs, @.DbName
exec (@.QueryIs)
GO
Any and all help is greatly appreciated!Did you cut and paste this code directly from MSSQAT (Microsoft SQL Server Query Analyzer Thingy)? Your use of double quotes is a syntax error. You have to use single quotes, and tripled single quotes to represent an embedded single quote.
Also, you use too many dots qualifying your table name.
@.DbName + "...Facility
should be
@.DbName + "..Facility
otherwise SQL Server will look be looking for a server named after your database. A fully qualified table reference looks like this:
[ServerName].[DatabaseName].[Owner].[Table]
Also, be aware that there are some connection settings that may have different defaults in Query Analyzer than other DB interfaces. For instance, SET CONCAT_NULL_YIELDS_NULL { ON | OFF } defaults to ON for SQL Query Analyzer, but defaults to OFF in Crystal reports.
blindman
Sunday, March 11, 2012
another question
please create a table in tempdb running following
USE tempdb
CREATE TABLE delete_me (c1 int, c2 int )
INSERT delete_me (c1, c2)
SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4
Then running the script below you can get (I do) the error message :
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'c3'.
But sometimes it works. The workaround seems to be to wrap UPDATE statement
up into EXEC, - it works always. Why is that?
BEGIN TRAN
ALTER TABLE delete_me
ADD c3 int
-- EXEC ('UPDATE delete_me SET c3 = 0')
UPDATE delete_me SET c3 = 0
ALTER TABLE delete_me
ALTER COLUMN c3 int NOT NULL
ROLLBACK TRAN
Thanks
AlexThats quite a normal behaviour. Object resolution takes place if the
object is already know so, this will fail due to the non existing
column. Look for
http://msdn.microsoft.com/library/d...>
_07_5wa6.asp
"Note Deferred Name Resolution can only be used when you reference
nonexistent table objects. All other objects must exist at the time the
stored procedure is created. For example, when you reference an
existing table in a stored procedure you cannot list nonexistent
columns for that table."
HTH, Jens Suessmeyer.|||AlexM
What is your SQL Server version?
It worked fine on my workstation (SS2000,SP3,Personal Edition)
"AlexM" <alex_remove_this_mak@.telus.net> wrote in message
news:CHYDf.157789$AP5.28253@.edtnps84...
> Hi guys,
> please create a table in tempdb running following
> USE tempdb
> CREATE TABLE delete_me (c1 int, c2 int )
> INSERT delete_me (c1, c2)
> SELECT 1, 1 UNION SELECT 2, 2 UNION SELECT 3, 3 UNION SELECT 4, 4
> Then running the script below you can get (I do) the error message :
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'c3'.
> But sometimes it works. The workaround seems to be to wrap UPDATE
> statement up into EXEC, - it works always. Why is that?
>
> BEGIN TRAN
> ALTER TABLE delete_me
> ADD c3 int
> -- EXEC ('UPDATE delete_me SET c3 = 0')
> UPDATE delete_me SET c3 = 0
> ALTER TABLE delete_me
> ALTER COLUMN c3 int NOT NULL
>
> ROLLBACK TRAN
>
> Thanks
> Alex
>|||Thanks Jens, that note apparently slipped my mind ;-)
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1138778592.061349.312360@.o13g2000cwo.googlegroups.com...
> Thats quite a normal behaviour. Object resolution takes place if the
> object is already know so, this will fail due to the non existing
> column. Look for
> http://msdn.microsoft.com/library/d...
es_07_5wa6.asp
> "Note Deferred Name Resolution can only be used when you reference
> nonexistent table objects. All other objects must exist at the time the
> stored procedure is created. For example, when you reference an
> existing table in a stored procedure you cannot list nonexistent
> columns for that table."
>
> HTH, Jens Suessmeyer.
>|||ss2000, enterprise & developer, sp4
Jens pointed to the note which explains clearly why it happens. What it
worries me though that this behaviour is not consistent. Most of the tine it
acts according to BOL and that particular note, but sometimes the resolution
stage comes through with flying colors when referencing a missing column for
existing table. But this is a bit different story...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eL4xwIwJGHA.3332@.TK2MSFTNGP11.phx.gbl...
> AlexM
> What is your SQL Server version?
> It worked fine on my workstation (SS2000,SP3,Personal Edition)
>
> "AlexM" <alex_remove_this_mak@.telus.net> wrote in message
> news:CHYDf.157789$AP5.28253@.edtnps84...
>
Another Question
Ingot 12345 12345 12345
Traveler 98786 87657 77675
Pieces 15 20 12
Step 1 x x x
Step 2 x x x
Step 3 x x x
Step 4 x x x
Step 5 x x x
Step 6 x x
Step 7 x x
...
Step 19
As you can see the columns for the steps are fixed. I know which step each
traveler is on so I want to populate the table with x's until I get to the
last step. As you can see from this report, the first and third travelers
are about the same and the second is stuck on step 5. The managers can look
at this and see that the second traveler will produce a hole later on. So
how could I create this table in SSRS? Keep in mind the steps are
predefined and will not change. So the information returned from the query
would be similar to the following
Ingot, Traveler, Pieces, Step
12345 98786 15 7
12345 87657 20 5
12345 77675 12 7
...
Thanks.On May 11, 2:39 pm, "John Wright" <riley_wrig...@.hotmail.com> wrote:
> I want to create a horizontal table as follows:
> Ingot 12345 12345 12345
> Traveler 98786 87657 77675
> Pieces 15 20 12
> Step 1 x x x
> Step 2 x x x
> Step 3 x x x
> Step 4 x x x
> Step 5 x x x
> Step 6 x x
> Step 7 x x
> ...
> Step 19
> As you can see the columns for the steps are fixed. I know which step each
> traveler is on so I want to populate the table with x's until I get to the
> last step. As you can see from this report, the first and third travelers
> are about the same and the second is stuck on step 5. The managers can look
> at this and see that the second traveler will produce a hole later on. So
> how could I create this table in SSRS? Keep in mind the steps are
> predefined and will not change. So the information returned from the query
> would be similar to the following
> Ingot, Traveler, Pieces, Step
> 12345 98786 15 7
> 12345 87657 20 5
> 12345 77675 12 7
> ...
> Thanks.
You will most likely want to use a matrix report where the pivoted
column (say Column_x) can have values: Ingot, Traveler and Pieces and
then have a normal column called Step. So your stored procedure/query
that will be sourcing the report should have an output like the
following:
Column_x Value Step
-- -- --
Ingot 12345 7
Traveler 98786 7
Pieces 15 7
Ingot 12345 5
Traveler 87657 5
Pieces 20 5
Ingot 12345 7
Traveler 77675 7
Pieces 12 7
Where Column_x will be the pivot column (it will turn into 3 columns
in the report: Ingot, Traveler and Pieces). Value will be the details
displayed for the pivot column. And Step is the row value (amounting
to a column). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Another query help question
again for your help.
CREATE TABLE [dbo].[Tracking] (
[key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
insert into tracking VALUES ('EVENT','verbal aggression')
insert into tracking VALUES ('EVENT','peer')
insert into tracking VALUES ('EVENT','bad behavior')
insert into tracking VALUES ('EVENT','other')
insert into tracking VALUES ('PRELIM','Loud noise')
insert into tracking VALUES ('PRELIM','agitation')
insert into tracking VALUES ('PRELIM','schedule change')
insert into tracking VALUES ('PRELIM','meal time')
CREATE TABLE [dbo].[Tracking_DATA] (
[ID_] [int] IDENTITY (1, 1) NOT NULL ,
[bts_ID_] [int] NULL ,
[key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
insert into tracking_DATA VALUES (1, 'EVENT', 'other')
insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
insert into tracking_DATA VALUES (1, 'PRELIM', 'Loud noise')
insert into tracking_DATA VALUES (1, 'PRELIM', 'agitation')
insert into tracking_DATA VALUES (2, 'EVENT', 'other')
insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
insert into tracking_DATA VALUES (2, 'PRELIM', 'Loud noise')
event prelim count
other loud noise 2
other agitation 1
bad behavior loud noise 1
bad behavior agitation 1
verbal aggression loud noise 1
peer <BLANK> 0Hi
Can you say how the information is grouped to get these values?
John
"Jack" wrote:
> Similar to my other question, but the counting is different. Thank you
> again for your help.
> CREATE TABLE [dbo].[Tracking] (
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
> insert into tracking VALUES ('EVENT','verbal aggression')
> insert into tracking VALUES ('EVENT','peer')
> insert into tracking VALUES ('EVENT','bad behavior')
> insert into tracking VALUES ('EVENT','other')
> insert into tracking VALUES ('PRELIM','Loud noise')
> insert into tracking VALUES ('PRELIM','agitation')
> insert into tracking VALUES ('PRELIM','schedule change')
> insert into tracking VALUES ('PRELIM','meal time')
>
> CREATE TABLE [dbo].[Tracking_DATA] (
> [ID_] [int] IDENTITY (1, 1) NOT NULL ,
> [bts_ID_] [int] NULL ,
> [key_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [value_] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> insert into tracking_DATA VALUES (1, 'EVENT', 'other')
> insert into tracking_DATA VALUES (1, 'EVENT', 'bad behavior')
> insert into tracking_DATA VALUES (1, 'PRELIM', 'Loud noise')
> insert into tracking_DATA VALUES (1, 'PRELIM', 'agitation')
> insert into tracking_DATA VALUES (2, 'EVENT', 'other')
> insert into tracking_DATA VALUES (2, 'EVENT', 'verbal aggression')
> insert into tracking_DATA VALUES (2, 'PRELIM', 'Loud noise')
>
> event prelim count
> other loud noise 2
> other agitation 1
> bad behavior loud noise 1
> bad behavior agitation 1
> verbal aggression loud noise 1
> peer <BLANK> 0
>
>
Another Problem with variables
declare
@.variable int
create table #tmp_datos
( variable int null )
insert into #tmp_datos
exec sp_prueba_era
select @.variable = variable
from
#tmp_datos
select @.variable
drop proc sp_prueba_era
go
create proc sp_prueba_era @.variable int output
as
declare
@.cmd varchar(250)
are there other way to do this??are you able to connect to your server using isql ?|||Yes, but I need to pass dinamically the name of the server, by this I'm using strings and exec...
But the original query it's this...
Select @.var = Select 1 from xx
I need to pass this to string...|||Originally posted by ramshree
are you able to connect to your server using isql ?
Not clear what you are trying to do.
but this works :
declare @.var char(100)
select @.var = 'select * from sysobjects'
exec (@.var)
-|||I'm trying to store a value in a variable into select statement, but my select statement it's a string that I'm going to execute with exec clause..., so I can't to do this...
declare
@.variable int,
@.cmd varchar(100)
select @.cmd = "select @.variable = 20"
exec (@.cmd)
because @.variable it's not a defined variable..., so how can I do for make a string like this...|||Maybe there is something, i haven't understood, but why is it so important, that the select is stored in the string?|||Hi,
Hope this is wat u want--
here i am assigning the count of the number of records to the variable @.varCountTable. but the whole string has to be executed in EXEC().
Personalize it to ur needs--
SET @.varSelectString = N'SELECT @.varCountTable = COUNT(*) FROM CPGSTAGEDB.DBO.' + @.varTableName
EXEC SP_EXECUTESQL @.varSelectString, N'@.varCountTable nvarchar(50) OUTPUT',@.varCountTable = @.varCountTable OUTPUT
Regards,
Ramya
Originally posted by ericka
I have this...
declare
@.variable int
create table #tmp_datos
( variable int null )
insert into #tmp_datos
exec sp_prueba_era
select @.variable = variable
from
#tmp_datos
select @.variable
drop proc sp_prueba_era
go
create proc sp_prueba_era @.variable int output
as
declare
@.cmd varchar(250)
are there other way to do this??
Another Noob question
SELECT PLNAME, PFNAME, PMNAME, DOB, ACCOUNT, SERVICE DATE
FROM MASTER, SLAVE
WHERE TECH='TECHNAME' AND(SERVICE DATE>='20060101' AND SERVICE DATE<='20061230')
ORDER BY SERVICE DATE
I am a noob, but I can't see this being complex.SELECT DISTINCT ...
might solve your problem.
Oh, yes ... this query has two tables. Where did you join them? If you do not do that, you'll get cartesian product of all values. Therefore, joining tables properly might be another possible solution. Such as
...
WHERE slave.some_column = master.some_column
AND ...|||Well, I tried the distinct and it made my outcome worse. I do not have a join to the tables because I am not clear how to do it yet.|||Well, you'd better figure that out :)
I have no idea how your tables look like (it is unclear which table contains which column you provided in the sample query), but seeing "DOB" (Date Of Birth?), you might have something like PERSON_ID in both tables which *might* be the column you are looking for.|||Agree with Littlefoot. You must join the tables by creating a relationship between the two. In order to do this, each of the tables must contain a common KEY upon which the tables are to be linked.
To create a relationship between the tables, click on the relationship icon on the toolbar, follow the steps that ultimately will permit you to make a connection between the common KEY, a field in the database, usually holding unique data for each record, eg. SS#.|||The join syntax is pretty straightforward
lets assume that TECH is the common column
SELECT PLNAME, PFNAME, PMNAME, DOB, ACCOUNT, SERVICE DATE
FROM MASTER
join SLAVE on Slave.TECH=master.tech
WHERE MASTER.TECH='TECHNAME' AND(SERVICE DATE>='20060101' AND SERVICE DATE<=#20061230#)
ORDER BY SERVICE DATE
incidentally I think you will have a problem with spaces in column names
I'd suggest developing or using a naming convention.
Personally I've always used something that distinguishs tables form columnsm used captialisation to make things easier to read, but there as many naming conventions as politicians want to grab all the cash in your pocket. Find one htat works for you, or your organistion and stick with it.
Select PLName, PFName, PMName, DoB, Acct, SrvDate
FROM DTMaster
join DTSlave on Slave.Tech=DTMaster.Tech
WHERE DTMaster.Tech='TECHNAME' AND(SERVICE DATE>='20060101' AND SrvDate<='20061230')
ORDER BY SrvDate
HTH|||I am using SQL server 2000. I am trying to create the simplist of queries in my mind and I keep getting duplications and its driving me nuts. I have two tables. (MASTER, SLAVE we will call them) All I am trying to do is "show me data within this date range" and it gives me results but duplicates. Help. PLEASE do not refer me to http://www.dbforums.com/t1201578.html because that is unclear to me! Here is my code:
SELECT PLNAME, PFNAME, PMNAME, DOB, ACCOUNT, SERVICE DATE
FROM MASTER, SLAVE
WHERE TECH='TECHNAME' AND(SERVICE DATE>='20060101' AND SERVICE DATE<='20061230')
ORDER BY SERVICE DATE
I am a noob, but I can't see this being complex.
What the others said, basically!
What columns correspond to what tables? Where is Tech? Is it on Master or Slave? The others are right; this syntax looks like it'll get you a cartesian product which isn't what you want. You want an inner join!
Also, if you're looking for a date range, maybe try using BETWEEN instead of 'SERVICE DATE>='20060101' AND SERVICE DATE<='20061230''
Wednesday, March 7, 2012
Another Connection Problem
I am starting to work with MSDE. I have no problem connecting to the Northwind db, but every time I create a new database, I can't open it.
I have used ...
"Data Source=MyTestDb;Initial Catalog=mytable;Integrated Security=SSPI;"
the error I receive is:
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'MyTestDb'. Login fails. Login failed for user 'HOODWKOTC124874\ASPNET'.
I have also used...
what I thought was the default user id =sa; password =;
I can make a winform and drop sql objcets on the page and connect to the db that I created, just not through asp.net
FYI- I do not have Enterprise manager.
Any help would be appreciated.
ThanksOpen Enterprise manager and create a login for ASPNET and grant it perms on the MyTable db.
Hope that helps
Kashif|||I don't have enterprise manager. I am using the MSDE.|||You can use OSQL, included with MSDE. You need to have the folder that OSQL is in on your path, or be in that folder.
Using the command prompt, type the following, substituting your COMPUTERNAME and databasename:
osql -S servername\instancename -E -q
--Line numbers will appear
EXEC sp_grantlogin 'COMPUTERNAME\ASPNET'
go
use <databasename>
go
EXEC sp_grantdbaccess 'COMPUTERNAME\ASPNET'
go
EXEC sp_addrolemember 'db_owner', 'COMPUTERNAME\ASPNET'
go
Another aggregation design question
Guys,
I model a cube with dimensions, I create the partitions and their aggregations, then I change the dimensions structure or I change the dataview.
Cube does not complain about aggregations at any time during the change process.
So what is this all about?
I suspect I should run a re-design of aggregations when I am done with structural changes but I wonder why I am never warned about existing aggregations becoming obsolete?
So when should you re-design aggregations? (I use 30% then I add usage based after a while).
Thanks,
Philippe
Yes, you need to go an re-desing your aggregations after you've made stuctural changes to your dimension: You've added, or removed attrubutes from the dimension, you've changed attribute relationships you should go and re-desing aggregations.
If you defined natural hierarchies ( you can search this forum for the definition of these) 30% should be a good start. But in case and if you are using just regual hierarchies without defining attribute relationships, or you browse attributes using attribute hierarchies, you should go over all the attributes you browse by and change AggregationUsage property to Full or Unrestricted.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Anonymous Subscriptions using SQLDMO
subscriptions in SQLDMO. Now I am trying to do anonymous subscriptions and I
am having a little difficulty.
I made sure to allow anonymous subscriptions at the publication. I
created an object of type MergePullSubscription2 and called it "sub" and set
the properties as follows:
sub.SubscriptionType = .SQLDMOSubscription_Anonymous;
sub.SubscriberType = .SQLDMOMergeSubscriber_Anonymous;
(and set all the other properties appropriately) and then added it to
the MergePullSubscriptions collection.
All of that works fine. But then I try to enable it at the publisher by
using the method EnableMergeSubscription (I make sure to use the constant
SQLDMOMergeSubscriber_Anonymous for the SubscriberType argument) and that
gives me the following error:
"[Microsoft][ODBC SQL Server Driver][SQL Server]The remote server is not
defined as a subscription server."
(this is strange because I can create other subscriptions that are not
anonymous)
Is this step (EnableMergeSubscription ) necessary? It seems to be
because if I try to sync, it fails.
It seems like anonymous subs should be pretty straightforward, but I
must be missing something fundamental.
Any help will be much appreciated.
Maer
Hello,
I suggest that you refer to the information on the following web site:
MergePullSubscription Object
http://msdn.microsoft.com/library/de...us/sqldmo/dmor
ef_ob_m_0egk.asp
To create a merge anonymous subscription at the Subscriber:
1.Create a new MergePullSubscription object.
2.Set the Publisher property to the name of an existing Publisher.
3.Set the PublicationDB property to the name of the database (at the
Publisher) where the publication is located.
4.Set the Publication property to the name of the publication to which to
subscribe.
5.Set the SubscriberType property to SQLDMOMergeSubscriber_Anonymous.
6.Set the SecurityMode property of the DistributorSecurity object property
as appropriate.
7.If the SecurityMode property of the DistributorSecurity object property
is set to SQLDMOReplSecurity_Normal, set the StandardLogin and
StandardPassword properties of the DistributorSecurity object property.
8.Set the SecurityMode property of the PublisherSecurity object property
as appropriate.
9.If the SecurityMode property of the PublisherSecurity object property is
set to SQLDMOReplSecurity_Normal, set the StandardLogin and
StandardPassword properties of the PublisherSecurity object property.
10.Note that the Name property defaults to
publisher:publication_database:publication.
11.Add the MergePullSubscription object to the MergePullSubscriptions
collection of a connected ReplicationDatabase object at the Subscriber.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Anonymous Subscription not showing as an option
Server 2000 (new server). I have successfully done this other
instances/servers. Why does the publication, which allows anonymous
subscritions, not show in my options?
I can ping the server just fine. I am sure I am just overlooking something.
Thanks-Deb
Is the account your SQL Server agent is running under on the subscriber in
the PAL on the Publisher?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Deb" <Deb@.discussions.microsoft.com> wrote in message
news:EC90B821-98B5-47F8-83B7-39C412EDC86D@.microsoft.com...
>I am attempting to create an anonymous subscription on a new install of SQL
> Server 2000 (new server). I have successfully done this other
> instances/servers. Why does the publication, which allows anonymous
> subscritions, not show in my options?
> I can ping the server just fine. I am sure I am just overlooking
> something.
> --
> Thanks-Deb
|||Yes... it is. I also listed this new server as a subscriber under Publisher
and Distributor properties.
Deb
"Deb" wrote:
> I am attempting to create an anonymous subscription on a new install of SQL
> Server 2000 (new server). I have successfully done this other
> instances/servers. Why does the publication, which allows anonymous
> subscritions, not show in my options?
> I can ping the server just fine. I am sure I am just overlooking something.
> --
> Thanks-Deb
|||Yes...it is. I also listed the server under Subscribers in the Publisher and
Distributor properties tab.
Deb
"Hilary Cotter" wrote:
> Is the account your SQL Server agent is running under on the subscriber in
> the PAL on the Publisher?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Deb" <Deb@.discussions.microsoft.com> wrote in message
> news:EC90B821-98B5-47F8-83B7-39C412EDC86D@.microsoft.com...
>
>
|||Is your subscriber by chance an MSDE subscriber?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Deb" <Deb@.discussions.microsoft.com> wrote in message
news:9D6E7DDD-6911-4E6A-BF9B-93001BEDADDC@.microsoft.com...[vbcol=seagreen]
> Yes...it is. I also listed the server under Subscribers in the Publisher
> and
> Distributor properties tab.
> --
> Deb
>
> "Hilary Cotter" wrote:
|||It should be able to see it then. Could you also try this - add the account
to the dbo group in the distribution database.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Deb" <Deb@.discussions.microsoft.com> wrote in message
news:2EB34604-83BE-4106-ABB9-3A88C2D2D941@.microsoft.com...[vbcol=seagreen]
> Yes... it is. I also listed this new server as a subscriber under
> Publisher
> and Distributor properties.
> --
> Deb
>
> "Deb" wrote:
|||I thought of that one as well. I added it just trying to cover my bases. No
go.
What I find interesting is I can see other Anonymous Snapshots available on
other servers and this user is not a part PAL or Sys Amin role on these
servers whatsoever.
Deb
"Hilary Cotter" wrote:
> It should be able to see it then. Could you also try this - add the account
> to the dbo group in the distribution database.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Deb" <Deb@.discussions.microsoft.com> wrote in message
> news:2EB34604-83BE-4106-ABB9-3A88C2D2D941@.microsoft.com...
>
>
Saturday, February 25, 2012
Announcing the Analysis Services Stored Procedure Project
A few months ago, a few community-spririted Analysis Services guys (including me) got together to create some example Analysis Services stored procedures. I'm happy to announce that beta 1 of our project is now available to download here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures
The idea was to create a set of useful extensions to MDX to help solve common problems and at the same time provide some example source code to help people writing their own stored procedures. Please take a look and tell us what you think!
Very Nice!
How about a function that takes care of divide-by-zero and returns NULL if the denominator is 0? Ie. ReturnDivide(division). That would clean up a lot of iif mdx.
|||We discussed this exact problem, but Mosha explained that using a sproc in this way would do more harm than good. Basically, the problem is that there's no way of marking a sproc as being deterministic (ie will always return the same result for the same cell) and so that means that if you use a sproc in a calculated member then the value returned by that calculation will never be cached. As a result, it's probably better to use IIF instead so that subsequent requests for the result returned by a calculation for any given cell in the cube will be returned from the cache.
To answer your other question about IIF, if <mdxstatement> is a calculated measure whose result can be cached, then no, it will only be executed once and the second time it's evaluated the value will be returned from the cache. So it's good idea to create a calculated measure to hold the value of <mdxstatement> even if you don't intend to display the result to the user and set its Visible property to False.
HTH,
Chris
|||Just so I understand:
You are saying that the following MDX script will make the server calculate [measures].[summation] once in the scope iif statement:
Create Member [Measures].[Summation] AS
Aggregate({[DimMember1], [DimMember2]});
Scope ([DimMemberX]);
this = iif([Measures].[Summation] = 0, NULL, [SomeSet] / [Mesures].[Summation];
End Scope;
Whereas this statement will make it calculate it twice:
Scope ([DimMemberX]);
this = iif(aggregate({[Dimmember1], [Dimmember2]}) = 0, NULL, [Someset] / aggregate([DimMember1], [DimMember2]));
End Scope;
|||Yes, that's what I understand.
Chris
Monday, February 13, 2012
Analysis services part of SQLServer ?
Should I install something ?
I need to create OLAP cubes in my SQLServer db.
ThanksThere's a profiler installed by default which helps you analyse how sql performs, what are it's bottlenecks and who/what/which gives it a hardtime. SQLServer keeps all sorts of statistics concerning a.o. table access.
I'm not sure how this translates to olap cubes though.|||According to the Features by Edition (http://www.microsoft.com/sql/evaluation/features/choosing.asp) table, Analysis Services is part of the Enterprise Edition of SQL Server 2000.
-PatP|||I need to create OLAP cubes in my SQLServer db.
Tell me if you are able to do that .. I always used to create stuff like Tables, Stored Procs triggers and functions in my SQL Server DB ...:cool:
and yes ... MS Analysis Server comes with SQL Server 2000 by default ... you just need to install it separately.
Analysis Services package in SQL2K
I'm new to Analysis Services package in SQL2K.
As data analyst I wanted to create a short presentation that would explore most advantages of using Analysis Services (Cube, datamart).
Questions:
1.Does this tool (Analysis Services) come with SQL2K?
2. Is there any light version (or client version)of Analysis Services?
3. How to start (what module I have to install , set up)
Any relevant info would be really appreciated.Thank you.
http://www.olapreport.com/market.htm
Yes Analysis Services (AS) comes free with SQL2K. If you / your organisation want to invest your money and effort into SQL Server at this point and time, I would recommend you to have a look at SQL 2005. The features and improvements to SQL2K are paramout.
You would use AS to build OLAP cubes on the server. Clients would use 3rd party products such as Pro Clarity / IA to access these cubes (to view this data, slice & dice). You can build local cubes on client if you really wanted to. Not recommended for various reasons, but nothing stopping you doing that.
Installation is very simple. Just put the CD and click on "SQL Server 2000 Components" and then select "Install Analysis Services". Please dont forget to install service pack (4 is the latest)
http://www.olapreport.com/Comment_Microsoft.htm
http://www.microsoft.com/sql/evaluation/bi/bianalysis.mspx
Thanks
Sutha
Sunday, February 12, 2012
Analysis Services Dimension Question
Any idea how to get such an "other" category?I just love SPAM. May I have another serving please ?
-PatP
Analysis Services Cube Editor
s
that by default only inner joins are created and to use Visual Database Tool
s
( a Visual Studio module). Any workarounds for this?
thanks in advanceThe most flexible way to do this is to create a view on the relational
database-side.
Analysis Services loads from a view as well as it can from a base table.
Remember however, that you should explicitly cast NULLs to specific values
from the outer join.
Analysis Services treats NULLs as 0's (in measures) and as unknown values
(as dimension keys).
In SQL Server 2005, we have expanded the semantics for handling NULLs, but
for now in SQL Server 2000, we strongly recommend that you specifically cast
NULLs to some pre-defined value prior to exposing the data to Analysis
Services. You will just be a lot happier if you do this.
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:8C2079B0-3156-485E-94B4-86A0A7A1F019@.microsoft.com...
> How does one create outer joins in the cube editor. The documentation
states
> that by default only inner joins are created and to use Visual Database
Tools
> ( a Visual Studio module). Any workarounds for this?
> thanks in advance
Analysis Services Cube Editor
that by default only inner joins are created and to use Visual Database Tools
( a Visual Studio module). Any workarounds for this?
thanks in advance
The most flexible way to do this is to create a view on the relational
database-side.
Analysis Services loads from a view as well as it can from a base table.
Remember however, that you should explicitly cast NULLs to specific values
from the outer join.
Analysis Services treats NULLs as 0's (in measures) and as unknown values
(as dimension keys).
In SQL Server 2005, we have expanded the semantics for handling NULLs, but
for now in SQL Server 2000, we strongly recommend that you specifically cast
NULLs to some pre-defined value prior to exposing the data to Analysis
Services. You will just be a lot happier if you do this.
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Homer" <Homer@.discussions.microsoft.com> wrote in message
news:8C2079B0-3156-485E-94B4-86A0A7A1F019@.microsoft.com...
> How does one create outer joins in the cube editor. The documentation
states
> that by default only inner joins are created and to use Visual Database
Tools
> ( a Visual Studio module). Any workarounds for this?
> thanks in advance
Analysis services connection problem
Hi,
I created a shared datasoure to point to a remote AS server, when I clicked "Test Connection" it succeeds.
I used the query builder to create an MDX query by dragging items from the cube, the results appear in the right hand paine when I do this which tells me that I am connecting to the cube successfully. I then finish the wizard which creates my report.
The problem comes when I click "Preview", I get the following error message:
An error occurred during local report processing.
An error occurred during report processing.
Cannot create a connection to data source 'SalesMartCube'
A connection cannot be made. Ensure that the server is running.
No connection could be made because the target machine actively refused it.
Why would I be able to see the query results in query builder but not when I preview the report?
Thanks,
Lachlan
Hi,
Did you have any joy with this as i am getting exactly the same problem?
Any advice would help.
Thanks
Thursday, February 9, 2012
Analysis Services Access Database
Hello,
I am trying to generate a cube in SSAS 2005 with an Access database as the data source. I can create the Data Source and successfully test the connection. Here is my connection string:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\DB\LSAM.mdb
I can also successfully create my Data Source View and I can return data by right clicking on any of my table objects and selecting 'Explore Data'
However, when I try to generate my cube, I receive the following error message:
OLE DB error: OLE DB or ODBC error: 'E:\DB\LSAM.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.; 3044.
Any assistance would be GREATLY appreciated.
Thank You,
Joshua Shull.
Try and import your Access database into SQL Server and build your cube from there.
Later you can try and change datasource to point to the access file and process it .
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.