SQL About AMO: write

Showing posts with label write. Show all posts

Sunday, March 25, 2012

Access Cube Detailed Info

Hello,

Could I write ActiveX Script in a DTS Package to return cube info (such as CubeName, CubeStatus, CubeSize, CubePartitions, CubeLastProcessed)? If that's
possible, I could then pump in the result into text file and import them into
a table for reporting purposes.

Please let me know if this is the best route to explore DSO objects or any alternative way of extracting this type of Cube data.

-Lawrence

Oops... this is a duplicate post due to network error. If you could respond to the previous posting, I would really appreciate it!

-Lawrence

Thursday, March 22, 2012

Access and extended properties

Hello,
Can I read and write the extended properties with Access?

If it’s possible is better create a project (ADP) or a classic MDB?

When I try with this query in the query panel (in a MDB)

SELECT objtype, objname, name, value
FROM fn_listextendedproperty
(NULL, 'schema', 'Person', 'table', default, NULL, NULL)

I have this error message:

The size of a field is too long

If I try with an ADP, when I ask a new query in design view, I have a message for some roblems between the Access version (2003) and SQL Server version (2005) and I can’t save the query.

Tanks.

Hi,

yes - there's no support for SQL Server 2005 in an Access 2003 ADP (you cant save tables, ,,,). If you have this combination it would be better to make a mdb file with odbc connection.... access 2007 supports sql server 2005 more or less...

if you create a mdb you can execute your sql query with ado or pt querys...

regards

audi

Access and extended properties

Hello,
Can I read and write the extended properties with Access?

If it’s possible is better create a project (ADP) or a classic MDB?

When I try with this query in the query panel (in a MDB)

SELECT objtype, objname, name, value
FROM fn_listextendedproperty
(NULL, 'schema', 'Person', 'table', default, NULL, NULL)

I have this error message:

The size of a field is too long

If I try with an ADP, when I ask a new query in design view, I have a message for some roblems between the Access version (2003) and SQL Server version (2005) and I can’t save the query.

Tanks.

Hi,

if you create a mdb you can execute your sql query with ado or pt querys...

regards

audi

Access a spaced column name

I have a table column like "First Name". How do I select that from the table.
It gives me an error if I write query:
select "First Name" from table1.
Any clues?
Thanks
VinnieI use brackets, likeSELECT [First Name]
FROM table1-PatP|||Thanks for the info

Thursday, March 8, 2012

about xp_cmdshell..

Hello there!
I'm using xp_cmdshell in a loop to write in a file...
do you know better way to improve effiently writing on disk in a loop?
or any recommandations on xp_cmdshell uses (could we switch off the
msg feedbacks ?)
thanks a lot
++
VinceDTS, BCP, COM or .NET all seem likely to be more efficient ways of
writing to a file. What type of file are you trying to write?
David Portas
SQL Server MVP
--|||On 31 Mar 2005 01:18:03 -0800, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>DTS, BCP, COM or .NET all seem likely to be more efficient ways of
>writing to a file. What type of file are you trying to write?
I'm trying to write csv files. this, in a loop cursor which 'travels'
over 4 millions records so far...I mean, I must write row after row
because some of them are being value-changed during looping ...So I
could not use of BCP...
but what about DTS? can we use it in a loop?
thanks
Vince|||I don't understand why you think you need to do this cursor-style. Why can't
you do a set-based UPDATE for whatever data changes you require and THEN use
DTS, BCP or whatever other method to do the export?
Yes, you can export data row-by-row using DTS, but that doesn't necessarily
mean that's the best way to do it. BCP is probably a better choice for
millions of rows of data.
Comma-separated files are a poor medium for data at the best of times. For 4
million rows it seems like there ought to be a better way. What is the targe
t
system for the exported data? Can't it support other more structured file
formats? If you must delimit, avoid commas and use TAB or pipe characters
instead.
David Portas
SQL Server MVP
--|||Hello,
xp_CmdShell is not an efficient way of writing to a file. Instead you
can try with some Active-X Dll which can be instantiated by SQL Server
using sp_OACreate call. This may be a better way of doing "writing to
an external file" especially when there are large number of rows.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D SQL Snippet
Exec @.hr =3D sp_OACreate @.tProgId, @.iobToken Output
If @.hr <> 0 Goto TrapOAErrors
Exec @.hr =3D sp_OASetProperty @.iobToken, N'FileName', @.strFileName
If @.hr <> 0 Goto TrapOAErrors
Exec @.hr =3D sp_OAMethod @.iobToken, N'OpenFileForWriting'
If @.hr <> 0 Goto TrapOAErrors
--
--
Exec @.hr =3D sp_OAMethod @.iobToken, N'WriteBufferToFile', @.bytesBuffer
If @.hr <> 0 Goto TrapOAErrors
--
--
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DSQL Snippet
Snippet VB Active-X DLL --
'--=3D=3DFunction for Opening file for Reading=3D=3D=BB
Public Function OpenFileForWriting()
hOrgFile =3D CreateFile(m_sFileName, GENERIC_READ, FILE_SHARE_READ Or
FILE_SHARE_WRITE, ByVal 0&, OPEN_EXISTING, 0, 0)
If hOrgFile <> 0 Then
SetFilePointer hOrgFile, 0, 0, FILE_BEGIN
End If
End Function
'--=3D=3DFunction for: Closing the File Handle=3D=3D=BB
Public Function CloseFile()
CloseHandle hOrgFile
'--=3D=3DClose the file=3D=3D=BB
End Function
'--=3D=3DFunction for Retrieving the FileReadBuffer=3D=3D=BB
Public Function WriteBufferToFile(tBuff() As Byte)
lRet As Long
Dim m_lOffset As Long
Err.Clear
'--=3D=3DClear the Error Device=3D=3D=
=BB
If hOrgFile =3D 0 Then
OpenFileForWriting
If hOrgFile =3D 0 Then
ReadBlockIntoBuffer =3D ""
Return
End If
End If
WriteFile hOrgFile, tBuff(1), UBound(tBuff), Ret, ByVal 0&
'--=3D=3DCheck for errors=3D=3D=BB
If lRet <> UBound(bBytes) Then 'Failed to READ the whole file
ErrorFlag =3D True
ErrorNumber =3D Err.Number
ErrorDesc =3D Err.Description
End If
End Function
Snippent Ends ---
The Above snippet is not complete and may not work :-)
Rgds ...
--Bju.|||If you have the option of including this in a .Net project, I would
recommend a nice normal select statement, using a sql reader, loop over
the reader and perform your row by row logic changes, and use my csv
parser/writer to create the file. The writer is not free however, but
is relatively cheap. At worst, I'd still recommend basically the same
way of doing it, using another way of creating the file in place of my
writer. The writer itself is going to create files at roughly 10 mb per
second.
http://www.geocities.com/shriop/index.html

Saturday, February 25, 2012

about system variable

i write a store procedure and it include
a lot of
insert into table1(col1,col2..)
select clo1,col2.. from table2
and i want get the @.@.error and @.@.rowcount
but if i write set @.myErr=@.@.error the @.@.rowcount will
turn to 1
and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
will ture into the error of 'set @.myRowcount=@.@.rowcount'
how can i get then both?thanksTry: SELECT @.myErr = @.@.ERROR, @.myRowCount = @.@.ROWCOUN T
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>|||Hi Frank
This was already answered in another newsgroup. Please do not post the same
question independently to multiple groups, so someone doesn't waste time
answering something that has already been answered.
Thanks
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:1280301c3c081$cffa56f0$a601280a@.phx.gbl...
> i write a store procedure and it include
> a lot of
> insert into table1(col1,col2..)
> select clo1,col2.. from table2
> and i want get the @.@.error and @.@.rowcount
> but if i write set @.myErr=@.@.error the @.@.rowcount will
> turn to 1
> and if i write set @.myRowcount=@.@.rowcount first,the @.@.error
> will ture into the error of 'set @.myRowcount=@.@.rowcount'
> how can i get then both?thanks
>

about store procedure

i have write several store procedure for export data,
most of the code such as :insert into Table1(col1,col2)
from select (col1,col2) form Table2,and i execute it in
sql analyser,and the analyser will show "x rows affected"
and now ,i want write a app with C#,call these store
procedures ,and want to konw
1,how many rows affected,analyser can return this?
2.how many rows selected,must i rewrite the store
procedure? such as add a new select count(*) from table2?
but this will decrease the perfermance
thank youCheck out SET NOCOUNT ON/OFF and @.@.ROWCOUNT in SQL Server Books Online.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you|||Frank..
One way some people do this is by returning a return status... Normally a
return status of 0 means the stored procedure was successfull, and a
negative return status means an error... Some people use a positive return
status to indicate how many rows were affected /select by the sp...
After the insert/select etc capture @.@.rowcount into a local variable..
declare @.error int, @.rowcount int
set nocount on
update ....
select @.rowcount = @.@.rowcount, @.error = @.@.error --I always capture
errors also
return @.rowcount
I always Set nocount ON as the first executable statement in an sp as well..
To use the return status
declare @.ret_status int
exec @.ret_status = myproc
Hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:061e01c3bd2e$b6fe4bd0$a001280a@.phx.gbl...
> i have write several store procedure for export data,
> most of the code such as :insert into Table1(col1,col2)
> from select (col1,col2) form Table2,and i execute it in
> sql analyser,and the analyser will show "x rows affected"
> and now ,i want write a app with C#,call these store
> procedures ,and want to konw
> 1,how many rows affected,analyser can return this?
> 2.how many rows selected,must i rewrite the store
> procedure? such as add a new select count(*) from table2?
> but this will decrease the perfermance
> thank you

Friday, February 24, 2012

about sorting

Hi all,
i use a full text for my db
however, when i write the SQL w/ order by and w/o order by
it's obviously time gap.
w/o using order by SQL, which return record within 5 seconds
w/ using order by SQL, which return record over 20 seconds
how can i elimiate the searching time by using order by SQL in fulltext
database ?
Thanks in advance!
Q,
Could you post the full output of SELECT @.@.version -- as this will provide
information about your environment. Could you also provide the total number
of rows in your FT-enabled table along with the expected number of rows to
be returned by your contains or freetext query?
Depending upon your answers, you may want to use CONTAINSTABLE or
FREETEXTTABLE and the Top_N_Rank parameter and restrict your results to
2000 rows or less and use an ORDER BY clause based upon RANK.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Q" <q@.q.com> wrote in message news:OZQlJmKmFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> i use a full text for my db
> however, when i write the SQL w/ order by and w/o order by
> it's obviously time gap.
> w/o using order by SQL, which return record within 5 seconds
> w/ using order by SQL, which return record over 20 seconds
> how can i elimiate the searching time by using order by SQL in fulltext
> database ?
> Thanks in advance!
>
|||"Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)"
actually, i have a 4 diff. full-text field, in one table which are (field_1,
field_2, field_3, field_4)
my query like this
select * from tblTest
where contains(field_1, '"my words"')
OR contains(field_2, '"my words"')
OR contains(field_3, '"my words"')
OR contains(field_4, '"my words"')
order by my_year desc, my_date desc
i MUST divided into 4 fields for searching, because sometimes, it only
involced field_1 for searching purpose!
"John Kane" <jt-kane@.comcast.net> glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...
> Q,
> Could you post the full output of SELECT @.@.version -- as this will provide
> information about your environment. Could you also provide the total
> number
> of rows in your FT-enabled table along with the expected number of rows to
> be returned by your contains or freetext query?
> Depending upon your answers, you may want to use CONTAINSTABLE or
> FREETEXTTABLE and the Top_N_Rank parameter and restrict your results to
> 2000 rows or less and use an ORDER BY clause based upon RANK.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Q" <q@.q.com> wrote in message
> news:OZQlJmKmFHA.1412@.TK2MSFTNGP09.phx.gbl...
>
|||Q,
First of all, thank you for providing the requested info!
You're using SQL Server 2000 SP3 on Win2K SP4 and therefore using the
infosoft.dll as the OS-supplied wordbreaker.
What is causing the slow performance is not the ORDER BY clause, but the
multiple CONTAINS clauses as each CONTAINS clause is a full "round trip",
i.e., each CONTAINS reads all of the entries in your FT Catalog, so the
below query is reading the FT Catalog four times! Unfortunately, this is "by
design" for SQL Server 2000. As a workaround, you may want to try using one
FREETEXT query and using the "*" (asterisk) for all FT-enabled columns, this
should perform better, but may give you more results than CONTAINS as
FREETEXT can search "across columns", and is less precise in its results
than CONTAINS.
See KB article 294809 (Q294809) "FIX: Full-Text Search Queries with CONTAINS
Clause Search Across Columns" at
http://support.microsoft.com/default...b;en-us;294809 for more info
as this behavior is also the default behavior for SQL Server 2000. You
should also consider using CONTAINSTABLE or FREETEXTTABLE with Top_N_Rank as
this parameter restricts the full "round trip" to just the Top N (some
specified number) by RANK.
FYI, SQL Server 2005 (codename Yukon) supports a new syntax of multiple
column syntax, for example:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
the above query is functionally equivalent to the following query:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
CONTAINS(FTSCol3, '"john" AND "kane"')
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Q" <q@.q.com> wrote in message news:#B73EXNmFHA.3316@.TK2MSFTNGP14.phx.gbl...
> "Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)"
> actually, i have a 4 diff. full-text field, in one table which are
(field_1,
> field_2, field_3, field_4)
> my query like this
> select * from tblTest
> where contains(field_1, '"my words"')
> OR contains(field_2, '"my words"')
> OR contains(field_3, '"my words"')
> OR contains(field_4, '"my words"')
> order by my_year desc, my_date desc
> i MUST divided into 4 fields for searching, because sometimes, it only
> involced field_1 for searching purpose!
>
> "John Kane" <jt-kane@.comcast.net>
glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...[vbcol=seagreen]
provide[vbcol=seagreen]
to
>
|||Thanks John Kane alot!
"John Kane" <jt-kane@.comcast.net> glsD:OFskTDQmFHA.2860@.TK2MSFTNGP15.phx.g bl...
> Q,
> First of all, thank you for providing the requested info!
> You're using SQL Server 2000 SP3 on Win2K SP4 and therefore using the
> infosoft.dll as the OS-supplied wordbreaker.
> What is causing the slow performance is not the ORDER BY clause, but the
> multiple CONTAINS clauses as each CONTAINS clause is a full "round trip",
> i.e., each CONTAINS reads all of the entries in your FT Catalog, so the
> below query is reading the FT Catalog four times! Unfortunately, this is
> "by
> design" for SQL Server 2000. As a workaround, you may want to try using
> one
> FREETEXT query and using the "*" (asterisk) for all FT-enabled columns,
> this
> should perform better, but may give you more results than CONTAINS as
> FREETEXT can search "across columns", and is less precise in its results
> than CONTAINS.
> See KB article 294809 (Q294809) "FIX: Full-Text Search Queries with
> CONTAINS
> Clause Search Across Columns" at
> http://support.microsoft.com/default...b;en-us;294809 for more
> info
> as this behavior is also the default behavior for SQL Server 2000. You
> should also consider using CONTAINSTABLE or FREETEXTTABLE with Top_N_Rank
> as
> this parameter restricts the full "round trip" to just the Top N (some
> specified number) by RANK.
> FYI, SQL Server 2005 (codename Yukon) supports a new syntax of multiple
> column syntax, for example:
> SELECT FTSPkey, FTSCol2, FTSCol3
> FROM FTS_Table
> WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
> the above query is functionally equivalent to the following query:
> SELECT FTSPkey, FTSCol2, FTSCol3
> FROM FTS_Table
> WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
> CONTAINS(FTSCol3, '"john" AND "kane"')
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Q" <q@.q.com> wrote in message
> news:#B73EXNmFHA.3316@.TK2MSFTNGP14.phx.gbl...
> (field_1,
> glsD:u978q1LmFHA.1192@.TK2MSFTNGP10.phx.g bl...
> provide
> to
>

Sunday, February 19, 2012

about search query

I would like to write query which can
1. ignore whether the search keyword is Upper case or lower Case
2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea

1. ignore whether the search keyword is Upper case or lower Case

SQL Queries are case insensitive.

2. deal with tense or verb form related problem
eg. comparing --> also search compare
Any Idea

No idea. I am unable to understand your question.
|||

sorry for my unclear idea!
What I want to do:
If people input the word in (a), the ColA contain word (b) will also be selected.
For example, select count(*) from tableA where ColA like '%compare%' , or sth like that

(a) (b)
1.CASW Casw

2.compare comparing

1. Since the keyword is in '', the case is sensitive, so Casw should not be select
2. Similary, since search compare will include provide result which have comparing,
Thx

|||I am sorry. I am still unable to understand your requirement. Can you try and xplain a little more?|||Thx for your patient!
Table A
Col_1 Col_hobby
Tom football, piano, reading, playing computer game, compare
Sam Football, piano, reading, comparing
When people use the query
select * from tableA where Col_hobby like '%Football%'
Only Tom record is selected, since the string in '' is sensitive the case. Any way to make it insensitve?
select * from tableA where Col_hobby like '%compare%'
Any way to make the query get the sam record?

|||Use the SQL Lower function to make the field in lower case and the searched keyword in lower case, that way you can get the desired result.|||you should get both records without having to worry about case.|||

ndinakar wrote:
you should get both records without having to worry about case.

Are you sure about that? I have tried several times a query like this:
select * from tableA where Col_hobby like '%Football%'
and it doesnt return the rows where col_hobby has the value 'football', but it does return the values with 'Football'|||

DECLARE @.tTABLE (colAvarchar(10), colBvarchar(100))

INSERTINTO @.tVALUES ('Tom','FOOTBALL,PIANO')

INSERTINTO @.tVALUES ('Sam','football,piano,volleyball')

SELECT*FROM @.tWHERE colBLIKE'%football%'
-------------

(1 row(s) affected)

colA colB

---- ------------------------------

Tom FOOTBALL,PIANO

Sam football,piano,volleyball

(2 row(s) affected)

|||Thanks for the followup ndinkar. I always that my query didnt worked.

Thursday, February 16, 2012

About ODBC + VC++

Hi,

I can't get ntext field value from my database (or nvarchar).

If I write in my database in english, I get only the 2 first letters.

If I write in my database in hebrew, I get gibbrish letters.

please I need some help

I'm using sql serser 2000, CRecordset, CDatabase, ODBC

Code Snippet

CDBVariant val;

rs.GetFieldValue("activity",val);

CString activity = (*activityVal.m_pstring).GetBuffer(0);

I'm sorry but I've not used MFC very much, but I've used ODBC, and this looks to me like you're fetching Unicode characters into ANSI buffers. The first thing I would try is defining your application as a Unicode app by adding the following add the beginning of your code:

#define UNICODE

#define _UNICODE

Here's a link to using ODBC via MFC: http://msdn2.microsoft.com/en-us/library/s9ds2ktb(VS.80).aspx

Hopefully someone else on this alias is more familiar with MFC to help you out.

Good luck!

~Warren

|||

Thank's,

Do you know how can I define these in my project? how to use this?

#define UNICODE

#define _UNICODE

About Members and Ordinal function

When I use MDX sample application,
I write a code like this:

SELECT

{[Time].[All Time].[2005]} ON COLUMNS,

{Filter([Equipment].Members, (InStr(1, [Equipment].CurrentMember.Name, "xxxx") =1))} ON ROWS

FROM francis.

Here Equipment is a huge dimension table,actually I want to do it like

Filter([Equipment].[All types].[mechanism].Members to narrow the range as prevent duplicate.
But it failed.

One more is I can't use ordinal function in it too, but I can do it in AS and test in pivottables.

Can someone tell me the reasons?
This is probably the same issue as I mentioned on the other thread - you probably need to use the .Children function instead of the .Members function. The .Children function returns all the members 'underneath' a member in a hierarchy, the .Members function returns all the members on a dimension or level. So:
[Equipment].[All types].[mechanism].Children
should work for you.

I'm not sure what you mean about your problem with the Ordinal function - can you provide more details?

Chris|||Hi, Chris, give you more details as example. I assume[Equipment].[All types].[mechanism].[machine001] have [abc],[bcd],[efg],[hij] these children like [Equipment].[All types].[mechanism].[machine001].[abc], and in them they all have a children call [ABSmain_xxxx] like [Equipment].[All types].[mechanism].[machine001].[abc].ABSmain_abc.
Now I want to average all the ABSmain_xxx value, so I will do a filter to the set I reach now.
If I use [Equipment].[All types].[mechanism].[machine001].Children, it will only filter the [abc],[bcd]...etc, it won't go deep to their children.
I suppose to do it like [Equipment].[All types].[mechanism].[machine001].[abc].level.members,I think it will work.

|||No, your last example won't do what you want, I think - it will return all the members on the same level as[Equipment].[All types].[mechanism].[machine001].[abc]. Does[Equipment].[All types].[mechanism].[machine001].[abc].CHILDREN do what you want? Otherwise, you might want to check out the DESCENDANTS function.

You might want to read some of the articles in the 'MDX Essentials' series here:
http://www.databasejournal.com/article.php/1459531/
Especially the ones on 'family' functions such as this one:
http://www.databasejournal.com/features/mssql/article.php/2168911

HTH,

Chris|||Hi, Chris, I have checked the descendants function and it is the one I want, it works fine expect for a little longer query time than I thought.

About Members and Ordinal function

When I use MDX sample application,
I write a code like this:

SELECT

{[Time].[All Time].[2005]} ON COLUMNS,

{Filter([Equipment].Members, (InStr(1, [Equipment].CurrentMember.Name, "xxxx") =1))} ON ROWS

FROM francis.

Here Equipment is a huge dimension table,actually I want to do it like

Filter([Equipment].[All types].[mechanism].Members to narrow the range as prevent duplicate.
But it failed.

One more is I can't use ordinal function in it too, but I can do it in AS and test in pivottables.

I'm not sure what you mean about your problem with the Ordinal function - can you provide more details?

Chris|||Hi, Chris, give you more details as example. I assume [Equipment].[All types].[mechanism].[machine001] have [abc],[bcd],[efg],[hij] these children like [Equipment].[All types].[mechanism].[machine001].[abc], and in them they all have a children call [ABSmain_xxxx] like [Equipment].[All types].[mechanism].[machine001].[abc].ABSmain_abc.
Now I want to average all the ABSmain_xxx value, so I will do a filter to the set I reach now.
If I use [Equipment].[All types].[mechanism].[machine001].Children, it will only filter the [abc],[bcd]...etc, it won't go deep to their children.
I suppose to do it like [Equipment].[All types].[mechanism].[machine001].[abc].level.members,I think it will work.

|||No, your last example won't do what you want, I think - it will return all the members on the same level as [Equipment].[All types].[mechanism].[machine001].[abc]. Does [Equipment].[All types].[mechanism].[machine001].[abc].CHILDREN do what you want? Otherwise, you might want to check out the DESCENDANTS function.

HTH,

Chris|||Hi, Chris, I have checked the descendants function and it is the one I want, it works fine expect for a little longer query time than I thought.

Thursday, February 9, 2012

About Count(Distinct) in AS

Hi,
Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
"Sum(IIF())" Syntax?
If it could be, how could I open or write Count(Distinct) or Sum(IIF())
function or syntax in Cube?
As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
Thanks!You can add an MDX expression in the cube definition, then the results of
the expression will be available as a cube cell, without re building the
cube...THis can be done in the cube editor of AS.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"angi" <angi@.microsoft.com> wrote in message
news:uuY2g6nfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
> "Sum(IIF())" Syntax?
> If it could be, how could I open or write Count(Distinct) or Sum(IIF())
> function or syntax in Cube?
> As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
> Thanks!
>

About Count(Distinct) in AS

Hi,
Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
"Sum(IIF())" Syntax?
If it could be, how could I open or write Count(Distinct) or Sum(IIF())
function or syntax in Cube?
As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
Thanks!
You can add an MDX expression in the cube definition, then the results of
the expression will be available as a cube cell, without re building the
cube...THis can be done in the cube editor of AS.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"angi" <angi@.microsoft.com> wrote in message
news:uuY2g6nfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
> "Sum(IIF())" Syntax?
> If it could be, how could I open or write Count(Distinct) or Sum(IIF())
> function or syntax in Cube?
> As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
> Thanks!
>

About Count(Distinct) in AS

Hi,
Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
"Sum(IIF())" Syntax?
If it could be, how could I open or write Count(Distinct) or Sum(IIF())
function or syntax in Cube?
As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
Thanks!You can add an MDX expression in the cube definition, then the results of
the expression will be available as a cube cell, without re building the
cube...THis can be done in the cube editor of AS.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"angi" <angi@.microsoft.com> wrote in message
news:uuY2g6nfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
> "Sum(IIF())" Syntax?
> If it could be, how could I open or write Count(Distinct) or Sum(IIF())
> function or syntax in Cube?
> As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
> Thanks!
>

about copy a new stored procedure to all databases

Dear all
i have been updating a new stored procedure to all database if it has
that procedure.
i have write something like to test with one database only but it
still fail.
it said i havent declare CID salary.
i cant figure out why and how. hope anyone have experience can help me
out.
Maybe can i update a procedure for a database and then copy them all
to other database.
thanks i really hope someone can help out for this
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name = 'cal_sal')
Begin
ALTER PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary [float] output)
AS
IF @.CID = 1234
Begin
xxxxxxxxxxxxxxxxxxxxxxx
/* it is the long statement*/
xxxxxxxxxxxxxxxxxxxxxxx
END
ENDCREATE/ALTER PROCEDURE must be the only statement in the batch so
CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
followed by unconditional CREATE:
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name ='cal_sal')
BEGIN
DROP PROCEDURE [dbo].[cal_sal]
END
GO
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary [float] output)
AS
IF @.CID = 1234
BEGIN
/* it is the long statement*/
END
GO
GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>|||Thanks Dan
you have really help me out
but while i would like to execute the statement, it said Create/Alter
procedure must be the first statement
why was this happen?
so my testing statement is like below:
use test
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name ='cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END
and finally i would like to use, is this possible ?
exec sp_MSforeachdb
'use [?]
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name ='cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'
On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> CREATE/ALTER PROCEDURE must be the only statement in the batch so
> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> followed by unconditional CREATE:
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> BEGIN
> DROP PROCEDURE [dbo].[cal_sal]
> END
> GO
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> BEGIN
> /* it is the long statement*/
> END
> GO
> GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <KelvinWon...@.gmail.com> wrote in message
> news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
>
> > Dear all
> > i have been updating a new stored procedure to all database if it has
> > that procedure.
> > i have write something like to test with one database only but it
> > still fail.
> > it said i havent declare CID salary.
> > i cant figure out why and how. hope anyone have experience can help me
> > out.
> > Maybe can i update a procedure for a database and then copy them all
> > to other database.
> > thanks i really hope someone can help out for this
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > ALTER PROCEDURE [dbo].[cal_sal]
> > (@.CID [varchar[50]],
> > @.Salary [float] output)
> > AS
> > IF @.CID = 1234
> > Begin
> > xxxxxxxxxxxxxxxxxxxxxxx
> > /* it is the long statement*/
> > xxxxxxxxxxxxxxxxxxxxxxx
> > END
> > END- -
> - -|||thanks so much
i also got that problem
On 8 1 , 10 47 , NEMA <realja...@.gmail.com> wrote:
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alterproceduremust be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>
> > CREATE/ALTERPROCEDUREmust be the only statement in the batch so
> > CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> > followed by unconditional CREATE:
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > BEGIN
> > DROPPROCEDURE[dbo].[cal_sal]
> > END
> > GO
> > CREATE PROCEDURE[dbo].[cal_sal]
> > (@.CID [varchar[50]],
> > @.Salary [float] output)
> > AS
> > IF @.CID = 1234
> > BEGIN
> > /* it is the long statement*/
> > END
> > GO
> > GRANT EXECUTE ONPROCEDURE[dbo].[cal_sal] TO MyRole
> > GO
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > <KelvinWon...@.gmail.com> wrote in message
> >news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
> > > Dear all
> > > i have been updating a newstoredprocedureto all database if it has
> > > thatprocedure.
> > > i have write something like to test with one database only but it
> > > still fail.
> > > it said i havent declare CID salary.
> > > i cant figure out why and how. hope anyone have experience can help me
> > > out.
> > > Maybe can i update aprocedurefor a database and thencopythem all
> > > to other database.
> > > thanks i really hope someone can help out for this
> > > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > > 'cal_sal')
> > > Begin
> > > ALTER PROCEDURE[dbo].[cal_sal]
> > > (@.CID [varchar[50]],
> > > @.Salary [float] output)
> > > AS
> > > IF @.CID = 1234
> > > Begin
> > > xxxxxxxxxxxxxxxxxxxxxxx
> > > /* it is the long statement*/
> > > xxxxxxxxxxxxxxxxxxxxxxx
> > > END
> > > END- -
> > - -- -
> - -|||put a GO before Create can fix the problem
but it still cannot use sp_MSforeachdb as it said there are syntax
error on create and end|||In article <1185968371.785331.165490@.x40g2000prg.googlegroups.com>,
KelvinWongYW@.gmail.com says...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>
add it to model so included in all new databases created from then
forward
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||thanks Pete
but how about the exisiting databases?
On 8 2 , 1 57 , Pete Berry <PeteBe...@.Caltech.edu> wrote:
> In article <1185968371.785331.165...@.x40g2000prg.googlegroups.com>,
> KelvinWon...@.gmail.com says...
>
> > Dear all
> > i have been updating a newstoredprocedureto all database if it has
> > thatprocedure.
> > i have write something like to test with one database only but it
> > still fail.
> > it said i havent declare CID salary.
> > i cant figure out why and how. hope anyone have experience can help me
> > out.
> > Maybe can i update aprocedurefor a database and thencopythem all
> > to other database.
> > thanks i really hope someone can help out for this
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > ALTER PROCEDURE[dbo].[cal_sal]
> > (@.CID [varchar[50]],
> > @.Salary [float] output)
> > AS
> > IF @.CID = 1234
> > Begin
> > xxxxxxxxxxxxxxxxxxxxxxx
> > /* it is the long statement*/
> > xxxxxxxxxxxxxxxxxxxxxxx
> > END
> > END
> add it to model so included in all new databases created from then
> forward
> --
> Graham (Pete) Berry
> PeteBe...@.Caltech.edu- -
> - -|||> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
You need a GO batch separator before the CREATE. SQL Server tools will send
the preceding batch of statements whenever a GO is encountered.
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
You'll need to wrap the batches individually with EXECUTE. Assuming you
want to skip system databases, try something like:
EXEC sp_MSForEachDB '
USE ?
IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
DROP PROC [dbo].[cal_sal]'')
EXEC(''CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'')
END
'
This technique is a bit of a kludge, though. IMHO, it's better to create a
script file and run for each database using OSQL or SQLCMD.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
>
>
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> CREATE/ALTER PROCEDURE must be the only statement in the batch so
>> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
>> followed by unconditional CREATE:
>> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> 'cal_sal')
>> BEGIN
>> DROP PROCEDURE [dbo].[cal_sal]
>> END
>> GO
>> CREATE PROCEDURE [dbo].[cal_sal]
>> (@.CID [varchar[50]],
>> @.Salary [float] output)
>> AS
>> IF @.CID = 1234
>> BEGIN
>> /* it is the long statement*/
>> END
>> GO
>> GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <KelvinWon...@.gmail.com> wrote in message
>> news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
>>
>> > Dear all
>> > i have been updating a new stored procedure to all database if it has
>> > that procedure.
>> > i have write something like to test with one database only but it
>> > still fail.
>> > it said i havent declare CID salary.
>> > i cant figure out why and how. hope anyone have experience can help me
>> > out.
>> > Maybe can i update a procedure for a database and then copy them all
>> > to other database.
>> > thanks i really hope someone can help out for this
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > ALTER PROCEDURE [dbo].[cal_sal]
>> > (@.CID [varchar[50]],
>> > @.Salary [float] output)
>> > AS
>> > IF @.CID = 1234
>> > Begin
>> > xxxxxxxxxxxxxxxxxxxxxxx
>> > /* it is the long statement*/
>> > xxxxxxxxxxxxxxxxxxxxxxx
>> > END
>> > END- -
>> - -
>|||Thanks
i am thinking of is there a way to loop through each database and then
Alter the store procedure by using OSQL or SQLCMD
however i cant find a command to loop and some database may not have
that stored procedure
On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > but while i would like to execute the statement, it said Create/Alter
> >proceduremust be the first statement
> > why was this happen?
> You need a GO batch separator before the CREATE. SQL Server tools will send
> the preceding batch of statements whenever a GO is encountered.
> > and finally i would like to use, is this possible ?
> > exec sp_MSforeachdb
> > 'use [?]
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > DROPPROCEDURE[dbo].[cal_sal]
> > End
> > CREATEPROCEDURE[dbo].[cal_sal]
> You'll need to wrap the batches individually with EXECUTE. Assuming you
> want to skip system databases, try something like:
> EXEC sp_MSForEachDB '
> USE ?
> IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
> BEGIN
> EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
> DROP PROC [dbo].[cal_sal]'')
> EXEC(''CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'')
> END
> '
> This technique is a bit of a kludge, though. IMHO, it's better to create a
> script file and run for each database using OSQL or SQLCMD.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "NEMA" <realja...@.gmail.com> wrote in message
> news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
>
> > Thanks Dan
> > you have really help me out
> > but while i would like to execute the statement, it said Create/Alter
> >proceduremust be the first statement
> > why was this happen?
> > so my testing statement is like below:
> > use test
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > DROPPROCEDURE[dbo].[cal_sal]
> > End
> > CREATEPROCEDURE[dbo].[cal_sal]
> > (@.CID int,
> > @.Salary float output)
> > AS
> > IF 2=2
> > BEGIN
> > select * from customer
> > END
> > and finally i would like to use, is this possible ?
> > exec sp_MSforeachdb
> > 'use [?]
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > DROPPROCEDURE[dbo].[cal_sal]
> > End
> > CREATEPROCEDURE[dbo].[cal_sal]
> > (@.CID int,
> > @.Salary float output)
> > AS
> > IF 2=2
> > BEGIN
> > select * from customer
> > END'
> > On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> > wrote:
> >> CREATE/ALTERPROCEDUREmust be the only statement in the batch so
> >> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> >> followed by unconditional CREATE:
> >> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => >> 'cal_sal')
> >> BEGIN
> >> DROPPROCEDURE[dbo].[cal_sal]
> >> END
> >> GO
> >> CREATE PROCEDURE[dbo].[cal_sal]
> >> (@.CID [varchar[50]],
> >> @.Salary [float] output)
> >> AS
> >> IF @.CID = 1234
> >> BEGIN
> >> /* it is the long statement*/
> >> END
> >> GO
> >> GRANT EXECUTE ONPROCEDURE[dbo].[cal_sal] TO MyRole
> >> GO
> >> --
> >> Hope this helps.
> >> Dan Guzman
> >> SQL Server MVP
> >> <KelvinWon...@.gmail.com> wrote in message
> >>news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
> >> > Dear all
> >> > i have been updating anewstoredprocedureto all database if it has
> >> > thatprocedure.
> >> > i have write something like to test with one database only but it
> >> > still fail.
> >> > it said i havent declare CID salary.
> >> > i cant figure out why and how. hope anyone have experience can help me
> >> > out.
> >> > Maybe can i update aprocedurefor a database and thencopythem all
> >> > to other database.
> >> > thanks i really hope someone can help out for this
> >> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => >> > 'cal_sal')
> >> > Begin
> >> > ALTER PROCEDURE[dbo].[cal_sal]
> >> > (@.CID [varchar[50]],
> >> > @.Salary [float] output)
> >> > AS
> >> > IF @.CID = 1234
> >> > Begin
> >> > xxxxxxxxxxxxxxxxxxxxxxx
> >> > /* it is the long statement*/
> >> > xxxxxxxxxxxxxxxxxxxxxxx
> >> > END
> >> > END- -
> >> - -- -
> - -|||Are you saying that you don't know which databases need the stored
procedure?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1186030181.537666.23780@.x40g2000prg.googlegroups.com...
> Thanks
> i am thinking of is there a way to loop through each database and then
> Alter the store procedure by using OSQL or SQLCMD
> however i cant find a command to loop and some database may not have
> that stored procedure
>
> On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> > but while i would like to execute the statement, it said Create/Alter
>> >proceduremust be the first statement
>> > why was this happen?
>> You need a GO batch separator before the CREATE. SQL Server tools will
>> send
>> the preceding batch of statements whenever a GO is encountered.
>> > and finally i would like to use, is this possible ?
>> > exec sp_MSforeachdb
>> > 'use [?]
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > DROPPROCEDURE[dbo].[cal_sal]
>> > End
>> > CREATEPROCEDURE[dbo].[cal_sal]
>> You'll need to wrap the batches individually with EXECUTE. Assuming you
>> want to skip system databases, try something like:
>> EXEC sp_MSForEachDB '
>> USE ?
>> IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
>> BEGIN
>> EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
>> DROP PROC [dbo].[cal_sal]'')
>> EXEC(''CREATEPROCEDURE[dbo].[cal_sal]
>> (@.CID int,
>> @.Salary float output)
>> AS
>> IF 2=2
>> BEGIN
>> select * from customer
>> END'')
>> END
>> '
>> This technique is a bit of a kludge, though. IMHO, it's better to create
>> a
>> script file and run for each database using OSQL or SQLCMD.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "NEMA" <realja...@.gmail.com> wrote in message
>> news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
>>
>> > Thanks Dan
>> > you have really help me out
>> > but while i would like to execute the statement, it said Create/Alter
>> >proceduremust be the first statement
>> > why was this happen?
>> > so my testing statement is like below:
>> > use test
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > DROPPROCEDURE[dbo].[cal_sal]
>> > End
>> > CREATEPROCEDURE[dbo].[cal_sal]
>> > (@.CID int,
>> > @.Salary float output)
>> > AS
>> > IF 2=2
>> > BEGIN
>> > select * from customer
>> > END
>> > and finally i would like to use, is this possible ?
>> > exec sp_MSforeachdb
>> > 'use [?]
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > DROPPROCEDURE[dbo].[cal_sal]
>> > End
>> > CREATEPROCEDURE[dbo].[cal_sal]
>> > (@.CID int,
>> > @.Salary float output)
>> > AS
>> > IF 2=2
>> > BEGIN
>> > select * from customer
>> > END'
>> > On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
>> > wrote:
>> >> CREATE/ALTERPROCEDUREmust be the only statement in the batch so
>> >> CREATE/ALTER cannot be conditional. Instead, consider a conditional
>> >> drop
>> >> followed by unconditional CREATE:
>> >> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> >> 'cal_sal')
>> >> BEGIN
>> >> DROPPROCEDURE[dbo].[cal_sal]
>> >> END
>> >> GO
>> >> CREATE PROCEDURE[dbo].[cal_sal]
>> >> (@.CID [varchar[50]],
>> >> @.Salary [float] output)
>> >> AS
>> >> IF @.CID = 1234
>> >> BEGIN
>> >> /* it is the long statement*/
>> >> END
>> >> GO
>> >> GRANT EXECUTE ONPROCEDURE[dbo].[cal_sal] TO MyRole
>> >> GO
>> >> --
>> >> Hope this helps.
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> <KelvinWon...@.gmail.com> wrote in message
>> >>news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
>> >> > Dear all
>> >> > i have been updating anewstoredprocedureto all database if it has
>> >> > thatprocedure.
>> >> > i have write something like to test with one database only but it
>> >> > still fail.
>> >> > it said i havent declare CID salary.
>> >> > i cant figure out why and how. hope anyone have experience can help
>> >> > me
>> >> > out.
>> >> > Maybe can i update aprocedurefor a database and thencopythem all
>> >> > to other database.
>> >> > thanks i really hope someone can help out for this
>> >> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> >> > 'cal_sal')
>> >> > Begin
>> >> > ALTER PROCEDURE[dbo].[cal_sal]
>> >> > (@.CID [varchar[50]],
>> >> > @.Salary [float] output)
>> >> > AS
>> >> > IF @.CID = 1234
>> >> > Begin
>> >> > xxxxxxxxxxxxxxxxxxxxxxx
>> >> > /* it is the long statement*/
>> >> > xxxxxxxxxxxxxxxxxxxxxxx
>> >> > END
>> >> > END- -
>> >> - -- -
>> - -
>

about copy a new stored procedure to all databases

About AMO

I write web service with .NET 2.0.

It's using AMO to connect SQL Server 2005 and show counts of database.

When I excute the program under debug mode in my computer for testing,it's showing the correct counts.

But when I establish this web in the server,and browse it to use, the showing counts is zero.

Could some tell me why?

Thanks~

Server OS: Windows 2003

My Computer OS: Windows XP

follow is program:

Code Snippet

<WebMethod()> _
Public Function Test() As String
Dim ConnectServer As New Server
Dim tmp As String
ConnectServer.Connect("Data Source = olapdw;Provider = MSOLAP;")

tmp = ConnectServer.Databases.Count
ConnectServer.Disconnect()
ConnectServer.Dispose()
Return tmp

End Function

It's most likely a security / authentication issue. AMO uses windows authentication, so running on your local machine might be running under your account. So it probably depends on what account is configured to run the application pool that your web service is running under. You can set up impersonation, but if the web service and IIS are on separate machines, you need to configure Kerberos authentication.

You also do not use a connection string for AMO, the connect string only needs a server name.

eg.

ConnectServer.Connect("olapdw")

|||

Thanks, my problem has already resolved.

It's the same like following link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2037078&SiteID=1&mode=1

About AMO

I write web service with .NET 2.0.

It's using AMO to connect SQL Server 2005 and show counts of database.

When I excute the program under debug mode in my computer for testing,it's showing the correct counts.

But when I establish this web in the server,and browse it to use, the showing counts is zero.

Could some tell me why?

Thanks~

Server OS: Windows 2003

My Computer OS: Windows XP

follow is program:

Code Snippet

<WebMethod()> _
Public Function Test() As String
Dim ConnectServer As New Server
Dim tmp As String
ConnectServer.Connect("Data Source = olapdw;Provider = MSOLAP;")

tmp = ConnectServer.Databases.Count
ConnectServer.Disconnect()
ConnectServer.Dispose()
Return tmp

End Function

You also do not use a connection string for AMO, the connect string only needs a server name.

eg.

ConnectServer.Connect("olapdw")

|||

Thanks, my problem has already resolved.

It's the same like following link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2037078&SiteID=1&mode=1

Sunday, March 25, 2012

Thursday, March 22, 2012

Thursday, March 8, 2012

Saturday, February 25, 2012

Friday, February 24, 2012

Sunday, February 19, 2012

Thursday, February 16, 2012

Thursday, February 9, 2012

Blog Archive

About Me