Calling Webservice: tables

Showing posts with label tables. Show all posts

Thursday, March 29, 2012

can i have the same parameter 3 times for different results?

I have 3 list/tables/columns..which ever is the easiest. I want to have a
parameter for the user to pick YEAR1 YEAR2 YEAR3 from drop down lists.
The only problem is when i make the first parameter, it doesnt let me make
another one, since its basically the same as the first, When i tried to make
a new one and change the name, it doesnt work. I basically want to have the
same parameter 3 times, for 3 different years. Which will show the measure
for those three different years based on what the user choses from the drop
down.
Year 1 Year 2 Year3
1 1 1
1 3 2
2 3 2
3 2 2
4 5 3
5 2 2
6 1 1
It seems like it would be easy, if it was 2 or 3 different parameters, but
since im working off a Cube (ANALYSIS SERVICES) when i click the parameter
check box in the dataset for the three different tables, nothing shows up
but the first parameter i made. Why is this? and how do i get it to give me
3 drop down boxes with the same field, but different values, based on the
users choice'Should these years be interchangeable, or could you use Year1, Year1+1 and
Year1+2?
If you could let the years be grouped together, you could stick with your
first parameter.
You might be able to create a few named sets or something, to make the
second and third year parameter. Not sure how, though. (And can't access
cubes right now, so can't check it out.)
On a side note, I've just given up on the whole new way of doing cube
queries. I usually do it the old school way. :)
Kaisa M. Lindahl Lervik
"Tenchy" <Tenchy@.discussions.microsoft.com> wrote in message
news:5BDD6564-F147-4FF5-B837-CD2E17569436@.microsoft.com...
>I have 3 list/tables/columns..which ever is the easiest. I want to have a
> parameter for the user to pick YEAR1 YEAR2 YEAR3 from drop down
> lists.
> The only problem is when i make the first parameter, it doesnt let me make
> another one, since its basically the same as the first, When i tried to
> make
> a new one and change the name, it doesnt work. I basically want to have
> the
> same parameter 3 times, for 3 different years. Which will show the measure
> for those three different years based on what the user choses from the
> drop
> down.
>
> Year 1 Year 2 Year3
> 1 1 1
> 1 3 2
> 2 3 2
> 3 2 2
> 4 5 3
> 5 2 2
> 6 1 1
> It seems like it would be easy, if it was 2 or 3 different parameters, but
> since im working off a Cube (ANALYSIS SERVICES) when i click the parameter
> check box in the dataset for the three different tables, nothing shows up
> but the first parameter i made. Why is this? and how do i get it to give
> me
> 3 drop down boxes with the same field, but different values, based on the
> users choice'
>

Can I have multiple tables to gather event data

Hi Guys,

Wondering if I could have two tables with parent child relationship to gather event data. Does NS support such scenario while defining schema in ADF.xml.

Please advice.

Thanks,

Shamir

You can use just about any T-SQL code in the EventRule Action node to

identify and insert new events for the SSNS application, as long as it

produces the appropriate fields as defined in the associated

eventclass.

HTH...

Joe Webb

SQL Server MVP

http://www.sqlns.com

~~~

Get up to speed quickly with SQLNS

http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.

(www.sqlpass.org)

On Sun, 23 Jul 2006 03:10:01 -0700,

wrote:

>Hi Guys,

>Wondering if I could have two tables with parent child relationship to

>gather event data. Does NS support such scenario while defining schema

>in ADF.xml.

>Please advice.

>Thanks,

>Shamir|||

[again, reposting since my prior attempt didn't seem to render appropriately]

You can use just about any T-SQL code in the EventRule Action node to
identify and insert new events for the SSNS application, as long as it
produces the appropriate fields as defined in the associated
eventclass.

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com

~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)

|||

Hi Joe,

Thanks for your reply.

Let me give you a clear picture of what I am trying to do ..

I have event data of following structure,

Table 1:

Table 2:

"TId" in table 2 references id of transaction element in table 1. Idea is to support unlimited number of rows in table2. How can i go about this.

Thanks,

Shamir

|||

Ahhh...gotcha.

A couple of options come to mind. First you can flatten out the rows into multiple events. In this scenario, you'd have one event row per transaction detail and that row would include information from table 1 and table 2. You'd want to then use the digest delivery to pull it all together into one notification. However, under certain circumstances, I could see how this may lead to an occassional mishap where a user would receive more than one notification, each being somewhat incomplete.

You could also look at submitting only the master events, table 1, and then retrieving the detail information later on in the process - in either the match rule or perhaps in a custom delivery protocol.

HTH...

Joe

Tuesday, March 27, 2012

Can I have an SQL view like in Access

If you open the Northwind.mdb in access and look at tables Orders &
Employees you'll see that both contain "Employee ID", but when you view the
Orders table you don't see the Employee ID you see the associated Employee's
name. Can this be done in SQL 2005? When I view the Orders table I see a
whole list of Employee ID which makes it hard to know which employee is
associated to the Employee ID. I have a real world application but I'm only
use Northwind as an example as everyone has this database.I don't use SQL 2005 but I am sure you can by creating a View combining
tables Orders and Employees with the EmployeeID as the linking Field.
--
HTH
Van T. Dinh
MVP (Access)
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||Hi.
> Can this be done in SQL 2005?
Not on the table itself. And you don't want to, either. It causes huge
problems. For more information, please see the article, "The Evils of
Lookup Fields in Tables," on the following Web page:
http://www.mvps.org/access/lookupfields.htm
> When I view the Orders table I see a whole list of Employee ID which makes
> it hard to know which employee is associated to the Employee ID.
You shouldn't be viewing the raw data in the tables. That's where the data
is stored, but to glean meaningful information from the data, you need to
use queries, forms or reports. In a query, you can join the two tables and
select the employee's first name and last name to display for each record,
not the EmployeeID. Create a similar view in SQL Server.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||I can see the "evils" of a lookup field in a table. I've never created a
view in SQL 2005 before... do you have a site that I could reference that
would give me a 101 on creating SQL 2005 views?
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
> Hi.
>> Can this be done in SQL 2005?
> Not on the table itself. And you don't want to, either. It causes huge
> problems. For more information, please see the article, "The Evils of
> Lookup Fields in Tables," on the following Web page:
> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
> You shouldn't be viewing the raw data in the tables. That's where the
> data is stored, but to glean meaningful information from the data, you
> need to use queries, forms or reports. In a query, you can join the two
> tables and select the employee's first name and last name to display for
> each record, not the EmployeeID. Create a similar view in SQL Server.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
> info.
>
> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know which
>> employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has this
>> database.
>|||A view is very simple to create. The basic syntax is
CREATE VIEW <viewname>
AS <select statement>
See these Books Online topics for the 101 information you need.
http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>I can see the "evils" of a lookup field in a table. I've never created a
>view in SQL 2005 before... do you have a site that I could reference that
>would give me a 101 on creating SQL 2005 views?
> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes huge
>> problems. For more information, please see the article, "The Evils of
>> Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the two
>> tables and select the employee's first name and last name to display for
>> each record, not the EmployeeID. Create a similar view in SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know
>> which employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has
>> this database.
>>
>|||One may also download the latest edition of SQL Server 2005 Books Online
(BOL), which also replaces previous editions of the SQL Server 2005 Express
BOL. Please see the following Web page for the download:
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>A view is very simple to create. The basic syntax is
> CREATE VIEW <viewname>
> AS <select statement>
> See these Books Online topics for the 101 information you need.
> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes huge
>> problems. For more information, please see the article, "The Evils of
>> Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the two
>> tables and select the employee's first name and last name to display for
>> each record, not the EmployeeID. Create a similar view in SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know
>> which employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has
>> this database.
>>
>>
>|||Since users shouldn't be viewing tables or even queries, you can simply base
your forms on queries that join the necessary tables. Forms should ALWAYS
be based on queries with selection criteria when the back end is not Jet.
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||sure, you can easily write a select statement to do this.
in the select statemetn are the columns you want to "see". in the
where section are the data rules that tell the engine how to get to
that data.
Absolutely, for sure, you do NOT have to have the "where" stuff in the
upper "select" stuff.|||I got the view working nicely, but I can't update any of the data from the
view. Is there a way around this or this is how it should be working?
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
message news:OGwfqlgbGHA.4892@.TK2MSFTNGP02.phx.gbl...
> One may also download the latest edition of SQL Server 2005 Books Online
> (BOL), which also replaces previous editions of the SQL Server 2005
> Express BOL. Please see the following Web page for the download:
> http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
> info.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>>A view is very simple to create. The basic syntax is
>> CREATE VIEW <viewname>
>> AS <select statement>
>> See these Books Online topics for the 101 information you need.
>> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes
>> huge problems. For more information, please see the article, "The
>> Evils of Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the
>> two tables and select the employee's first name and last name to
>> display for each record, not the EmployeeID. Create a similar view in
>> SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you
>> view the Orders table you don't see the Employee ID you see the
>> associated Employee's name. Can this be done in SQL 2005? When I view
>> the Orders table I see a whole list of Employee ID which makes it hard
>> to know which employee is associated to the Employee ID. I have a real
>> world application but I'm only use Northwind as an example as everyone
>> has this database.
>>
>>
>>
>|||It depends on if it's an updatable view or not. The
conditions on when it's updatable or not are listed in books
online in the Create View topic. There is a section titled
Updatable Views.
-Sue
On Fri, 12 May 2006 13:15:39 -0400, "M.Siler"
<John.Doe@.NoSpam.com> wrote:
>I got the view working nicely, but I can't update any of the data from the
>view. Is there a way around this or this is how it should be working?
>
>"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
>message news:OGwfqlgbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>> One may also download the latest edition of SQL Server 2005 Books Online
>> (BOL), which also replaces previous editions of the SQL Server 2005
>> Express BOL. Please see the following Web page for the download:
>> http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
>> news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>>A view is very simple to create. The basic syntax is
>> CREATE VIEW <viewname>
>> AS <select statement>
>> See these Books Online topics for the 101 information you need.
>> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes
>> huge problems. For more information, please see the article, "The
>> Evils of Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the
>> two tables and select the employee's first name and last name to
>> display for each record, not the EmployeeID. Create a similar view in
>> SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you
>> view the Orders table you don't see the Employee ID you see the
>> associated Employee's name. Can this be done in SQL 2005? When I view
>> the Orders table I see a whole list of Employee ID which makes it hard
>> to know which employee is associated to the Employee ID. I have a real
>> world application but I'm only use Northwind as an example as everyone
>> has this database.
>>
>>
>>
>>
>

can i grant all privilege to user?

it is possible to grant all privilege (CRUD) to specified table to user. But, now, i want to grant all privilege (CRUD) of all tables, views, sp, ... of database to the user. is it possible?

regards,

Yes you can try the link below for SQL Server object permissions. It can get complex but the link below can get you started. You have the option of doing it with T-SQL or with GUI with Management Studio. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms188371.aspx

|||Now, i assign a role(db_owner) to the user. Because i don't know the different permissions of the different role, i just assign as db_owner. In my case, the only needed is DDL, CRUD DML, stored procedure. That's all. So, is there any other more suitable role. As i can't find the way that assign all these privilege only one command, i just assign a role. I don't know whether is it good or not.

Any idea?|||

You have two options dbcreator and db_ddladmin roles both are less than dbo and may do what you need because a of service pack3 in SQL Server 2000 dbcreator cannot change ownership of a database. The links below will help with more details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms175892.aspx

http://msdn2.microsoft.com/en-us/library/ms189121.aspx

|||thx for ur kindness.|||I am glad I could help.sql

Can I get URL for RS from database ReportServer?

Hi,
is there any way to get URL for RS from database ReportServer?
I looked through tables in this DB but didn't find anything helpful.
Thanks.
VictorDid you mean you want to know what URL the report server is using?
Check C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config
"Victor" wrote:
> Hi,
> is there any way to get URL for RS from database ReportServer?
> I looked through tables in this DB but didn't find anything helpful.
> Thanks.
> Victor
>|||Thanks, it has it.
But I need this to find URLs for all Reporting Services on the network. So
maybe this is not the best method.
Any other ideas?
Victor
"David Bienstock" wrote:
> Did you mean you want to know what URL the report server is using?
> Check C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config
> "Victor" wrote:
> > Hi,
> >
> > is there any way to get URL for RS from database ReportServer?
> >
> > I looked through tables in this DB but didn't find anything helpful.
> >
> > Thanks.
> > Victor
> >|||Download SQLH2 and its ReportingServices reports... neat stuff like
performance counters, uptime, and all in RS!
"Victor" wrote:
> Thanks, it has it.
> But I need this to find URLs for all Reporting Services on the network. So
> maybe this is not the best method.
> Any other ideas?
> Victor
> "David Bienstock" wrote:
> > Did you mean you want to know what URL the report server is using?
> >
> > Check C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\ReportServer\RSReportServer.config
> >
> > "Victor" wrote:
> >
> > > Hi,
> > >
> > > is there any way to get URL for RS from database ReportServer?
> > >
> > > I looked through tables in this DB but didn't find anything helpful.
> > >
> > > Thanks.
> > > Victor
> > >|||Thanks again. And again it won't work for me.
I need to do this during run time. I want to populate combo box on my form
with RS URLs even if there is only one RS on the network.
Victor
"David Bienstock" wrote:
> Download SQLH2 and its ReportingServices reports... neat stuff like
> performance counters, uptime, and all in RS!
> "Victor" wrote:
> > Thanks, it has it.
> >
> > But I need this to find URLs for all Reporting Services on the network. So
> > maybe this is not the best method.
> >
> > Any other ideas?
> >
> > Victor
> >
> > "David Bienstock" wrote:
> >
> > > Did you mean you want to know what URL the report server is using?
> > >
> > > Check C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > > Services\ReportServer\RSReportServer.config
> > >
> > > "Victor" wrote:
> > >
> > > > Hi,
> > > >
> > > > is there any way to get URL for RS from database ReportServer?
> > > >
> > > > I looked through tables in this DB but didn't find anything helpful.
> > > >
> > > > Thanks.
> > > > Victor
> > > >

Can I generate Table Script in a Stored Procedure?

I have to load around 40 tables and I want to write one Stored Procedure that I pass a table name into. Data is coming from 3 different sources and to fill each of the 40 tables. I want to create a temp table that I can load first to make sure it succeeds first before I truncate and insert into the production table.

Is there a system procedure or something that will allow me to create a temp table from a table that alresdy exist in my database?

You can use SELECT INTO to create a new table based on a query like:

SELECT t.col1, t.col2, t.col3

INTO #t

FROM your_table AS t

Above query will create table with same structure as your_table without the defaults, constraints, indexes etc. Identity property on columns will be transferred though. You may also want to look at SSIS to automate your bulk loading process. You can also take a look at the link below for more pointers on how to improve the bulk load process:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

Note that with above query, you can create permanent table too. If you create a temporary table then you can only use BULK INSERT statement since the temporary table is connection scoped. And I am not sure if SSIS can perform SELECT...INTO and bulk load from the client side within context of single connection (You will have to ask in the SSIS forum).

|||

Good to know you can create Temps like this.

I might be able to work with this let me know what you think.

Problem:

I’m getting my data from an AS400 using

Insert Into @.TableName

Select *

from Opendatasource(“Connection Info”).AS400ServerName.AS400Library.@.TableName

The table name and column structure in the AS400 are the same as in my database but the column type is different i.e. A lot of the AS400 columns are Char when I need them to be Decimal. I need to create the temp table with the same structure as my table because the majority of the time my process fails is because the AS400 has Char data when it should be Decimal

My Solution:

Select Top 1 *

Into #t

From @.TableName

Truncate Table #t

--Insert temp data from sites

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.California.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Nevada.@.TableName

Insert Into #t

Select *

from Opendatasource(“Connection Info”).AS400ServerName.Arizona.@.TableName

Truncate Table @.TableName

--Insert data to Production

Insert Into @.TableName

Select *

From #t

Sunday, March 25, 2012

Can I fill a cursor from a strored procedure?

Basically, I have a complex stored procedure that combines two tables and fills a cursor.

I would like to fill another cursor in another stored procedure from the results of this first stored proc, rather than have to type it all in again.

The reason being that I am doing a one time import of some data from two tables into one new table based on some complex linking/querying.

Can I fill a cursor from the output of another stored procedure rather than an inline SELECT statement?

Does the sp I am using have to have cursor as an out parameter?a more easier way would be to create a table with all the columns tht your first cursor returns...so when you combine the 2 tables and fill the cursor...insert those records into the table..now you have a table with the records...so you can use a select statement on the table itself in the second stored proc...

HTH|||I would rather use the table option. But since the tables are shared between stored procedures then you need to use the local temp table or global temp tables.

But there are options to pass a cursor back to the calling procedure ... You would need to use an output cursor parameter in your stored procedure. Note that cursor parameters can be only output & use the keyword VARYING since the resultset supported is created dynamically by the stored procedure and whose contents can vary. Something along the lines of

CREATE PROCEDURE usp_cursor
@.orders_cursor CURSOR VARYING OUTPUT
AS
SET @.orders_cursor = CURSOR FOR
SELECT *
FROM Northwind..Orders
WHERE Freight > 100.00
OPEN @.titles_cursor
GO

You can now execute this stored procedure in a batch which declares a local cursor and assign the output cursor parameter value to the local cursor variable like :

DECLARE @.local_crsr CURSOR
EXEC usp_cursor @.orders_cursor = @.local_crsr OUTPUT
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @.local_crsr
...
END
CLOSE @.local_crsr
DEALLOCATE @.local_crsr|||hi
i dont have any idea about the topic cursor,can you give me a certain link that it can help me to understand very well this topic and thank you for your help|||Article on cursorssql

can I export tables so that existing tables in destination database will be modified?

I'm working on an ASP.Net project where I want to test code on a local
machine using a local database as a back-end, and then export it to
the production machine where it uses the hosting provider's SQL Server
database on the back-end. Is there a way to export tables from one
SQL Server database to another in such a way that if a table already
exists in the destination database, it will be updated to reflect the
changes to the local table, without existing data in the destination
table being lost? e.g. suppose I change some tables in my local
database by adding new fields. Can I "export" these changes to the
destination database so that the new fields will be added to the
destination tables (and filled in with default values), without losing
data in the destination tables?

If I run the DTS Import/Export Wizard that comes with SQL Server and
choose "Copy table(s) and view(s) from the source database" and choose
the tables I want to copy, there is apparently no option *not* to copy
the data, and since I don't want to copy the data, that choice doesn't
work. If instead of "Copy table(s) and view(s) from the source
database", I choose "Copy objects and data between SQL Server
databases", then on the following options I can uncheck the "Copy
Data" box to prevent data being copied. But for the "Create
Destination Objects" choices, I have to uncheck "Drop destination
objects first" since I don't want to lose the existing data. But when
I uncheck that and try to do the copy, I get collisions between the
properties of the local table and the existing destination table,
e.g.:
"Table 'wbuser' already has a primary key defined on it."

Is there no way to do what I want using the DTS Import/Export Wizard?
Can it be done some other way?

-Bennett"Bennett Haselton" <bennett@.peacefire.org> wrote in message
news:e614455c.0411160123.294dfd10@.posting.google.c om...
> I'm working on an ASP.Net project where I want to test code on a local
> machine using a local database as a back-end, and then export it to
> the production machine where it uses the hosting provider's SQL Server
> database on the back-end. Is there a way to export tables from one
> SQL Server database to another in such a way that if a table already
> exists in the destination database, it will be updated to reflect the
> changes to the local table, without existing data in the destination
> table being lost? e.g. suppose I change some tables in my local
> database by adding new fields. Can I "export" these changes to the
> destination database so that the new fields will be added to the
> destination tables (and filled in with default values), without losing
> data in the destination tables?

In a word, no.

I'd suggest take the same version controlled scripts (you are of course
using version control, right? :-) you used to make the changes in Dev and
run those against the Prod server.

> If I run the DTS Import/Export Wizard that comes with SQL Server and
> choose "Copy table(s) and view(s) from the source database" and choose
> the tables I want to copy, there is apparently no option *not* to copy
> the data, and since I don't want to copy the data, that choice doesn't
> work. If instead of "Copy table(s) and view(s) from the source
> database", I choose "Copy objects and data between SQL Server
> databases", then on the following options I can uncheck the "Copy
> Data" box to prevent data being copied. But for the "Create
> Destination Objects" choices, I have to uncheck "Drop destination
> objects first" since I don't want to lose the existing data. But when
> I uncheck that and try to do the copy, I get collisions between the
> properties of the local table and the existing destination table,
> e.g.:
> "Table 'wbuser' already has a primary key defined on it."
> Is there no way to do what I want using the DTS Import/Export Wizard?
> Can it be done some other way?
> -Bennett|||"Greg D. Moore $Strider$" <mooregr_deleteth1s@.greenms.com> wrote in message news:<7Mnmd.16$1u.15@.twister.nyroc.rr.com>...
> "Bennett Haselton" <bennett@.peacefire.org> wrote in message
> news:e614455c.0411160123.294dfd10@.posting.google.c om...
> > I'm working on an ASP.Net project where I want to test code on a local
> > machine using a local database as a back-end, and then export it to
> > the production machine where it uses the hosting provider's SQL Server
> > database on the back-end. Is there a way to export tables from one
> > SQL Server database to another in such a way that if a table already
> > exists in the destination database, it will be updated to reflect the
> > changes to the local table, without existing data in the destination
> > table being lost? e.g. suppose I change some tables in my local
> > database by adding new fields. Can I "export" these changes to the
> > destination database so that the new fields will be added to the
> > destination tables (and filled in with default values), without losing
> > data in the destination tables?
> In a word, no.
> I'd suggest take the same version controlled scripts (you are of course
> using version control, right? :-) you used to make the changes in Dev and
> run those against the Prod server.

Uh, no... my "script" is that I move the mouse pointer over the
"wbuser" table in Server Explorer in VS .Net, right-click, pick
"Design Table", and add columns that way :)

So I guess I'll just have to remember to run that "script" against the
Prod database whenever I make any changes.

Still, I would have thought that what I was trying, would be a pretty
common thing for people to want to do... surprised it's not built-in.

-Bennett|||Bennett Haselton (bennett@.peacefire.org) writes:
> Uh, no... my "script" is that I move the mouse pointer over the
> "wbuser" table in Server Explorer in VS .Net, right-click, pick
> "Design Table", and add columns that way :)
> So I guess I'll just have to remember to run that "script" against the
> Prod database whenever I make any changes.
> Still, I would have thought that what I was trying, would be a pretty
> common thing for people to want to do... surprised it's not built-in.

There are third-party tools. Red Gate's SQL Compare is popular.

One reason that this is not built-in is that changes to tables is far
from always something that can be performed automatically. In our shop
we keep all table definitions under source control. To generate an update
script we have a tool that reads SourceSafe between two labels to load
all new and changed objects. For changed tables you get a script that
copies data from the old definition to the new, but this script is really
only a template. In many cases I need to change the script, because a
new column is supposed be initiated with some values derived from
other columns or tables. And there might be even wilder changes.

Point and click may be convenient, but for something which is to be
run several times needs to be automized to be sure that all are updates
are carried out the same way. Thus, you need a script.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Can I do this in SQL Analyzer or with a dts package?

I am not sure how to approach this problem or if I can even do it
within SQL.

Say I have 2 tables invoice_hdr and invoice_dtl. I want to pull
specific fields from both tables, I want the data to be grouped
together however I want the information to be on different lines. One
for the information from the header and one for each corresponding
detail record.

So my output might look like

Invoice_number (1), Invoice_date, Customer_Number
Item_number (1-1), qty, price, description
Item_number (1-2), qty, price, description

Invoice_number (2), Invoice_data, Customer_number
Item_number (2-1), qty, price, description
Item_number (2-2), qty, price, description
Item_number (2-3), qty, price, description

etc..
So the invoice_number (1) is my first invoice and item_number (1-1)
would be the 1st item on tins invoice, etc...

My ultimate goal is to export this to a text or csv file.
Thanks.Hi Mike,

It can be done in a SQL stored proc but it will get kind of ugly. Basically
it sounds like you are taking two tables and trying to return one result set
(with 1 schema) or maybe just one text (varchar) column with all the data
written and concatenated as text.

Using VB Script, or a console application written in C, VB.Net or something
you could have your stored proc return two result sets in Invoice Number
order and then create the text (CSV file) by marching through the two
recordsets (if you are using ADO) and outputing records to your text file in
the application that calls the stored proc.

If you use VB Script you could put that in a DTS Package, Call the stored
proc and create the output file.

What is the schema of invoice_hdr and invoice_dtl?

-Dick Christoph
"mike" <mike.a.rea@.gmail.com> wrote in message
news:1141241610.678093.61540@.v46g2000cwv.googlegro ups.com...
>I am not sure how to approach this problem or if I can even do it
> within SQL.
> Say I have 2 tables invoice_hdr and invoice_dtl. I want to pull
> specific fields from both tables, I want the data to be grouped
> together however I want the information to be on different lines. One
> for the information from the header and one for each corresponding
> detail record.
> So my output might look like
> Invoice_number (1), Invoice_date, Customer_Number
> Item_number (1-1), qty, price, description
> Item_number (1-2), qty, price, description
> Invoice_number (2), Invoice_data, Customer_number
> Item_number (2-1), qty, price, description
> Item_number (2-2), qty, price, description
> Item_number (2-3), qty, price, description
> etc..
> So the invoice_number (1) is my first invoice and item_number (1-1)
> would be the 1st item on tins invoice, etc...
> My ultimate goal is to export this to a text or csv file.
> Thanks.|||If you are pushing this through some sort of parser, grid, asp page,
etc., here's what I usually do...

1) Use correlated tables with two datasets. Google search for
that--some of the .net controls support it via nesting.

2) Return everything on every record. e.g.
Invoice_number (1), Invoice_date, Customer_Number, Item_number (1-1),
qty, price, description
Invoice_number (1), Invoice_date, Customer_Number, Item_number (1-2),
qty, price, description
Invoice_number (1), Invoice_date, Customer_Number, Item_number (1-3),
qty, price, description

Then when you display this, just check neighbor rows (pseudocode):

For Each Row
If PreviousRow.InvoiceNumber <> CurrentRow.InvoiceNumber Then
Output Header Row (invoicenum, date, cust num)
Output Detail row(item number, qty, price, description)
Else
Output Detail row(item number, qty, price, description)
End If
Next

Sure this approach returns some data you won't use for every row but it
is very easy to do, and involves just one record set.

You could presumably implement this approach or DickChristoph's idea in
a cursor, too.sql

Thursday, March 22, 2012

Can I create table with constrains in SMO?

Hello Guys,
I m new to SMO thing. Just wonder if we can define data constrains in SMO for tables?
Cheers,
Elton

The simple answer is yes, anything you can do in SSMS you can do using SMO. Can you offer any specifics regarding what you want to do?

|||

I wrote a tutorial on how to create a table in SQL Server using SMO. This may get you started:

Create Table in SQL Server 2005 Using C# and SQL Server Management Objects (SMO) - Code Generation

Regards,

Dave

Can I create OLAP Cubes without a staging area?

I have de normalised tables in the source for use in a star
schema.However the grain of the facts that I may need are different
from that in the source also the source database has got denormalised
database which has data according to the three diferent companies
where the data is generated...
Can i build views not move the data and create cubes...how do i deal
with Unassiged if I do so?
Does anyone have an Idea how to deal with unassigned values without
creating a fact table based just on views from the source?
Thanks in anticipation...
AjayYes you can certainly build cubes off relational sources, to handle
unassigned values, you could use the isnull function. If you do end up using
a fact table, you can create "unknown" values in your dim tables that still
link to the fact table.
Not clear if this is what you are asking or not..
Ray Higdon MCSE, MCDBA, CCNA
--
"Ajay Garg" <ajayz90@.hotmail.com> wrote in message
news:d9477327.0403170556.173c077c@.posting.google.com...
> I have de normalised tables in the source for use in a star
> schema.However the grain of the facts that I may need are different
> from that in the source also the source database has got denormalised
> database which has data according to the three diferent companies
> where the data is generated...
>
> Can i build views not move the data and create cubes...how do i deal
> with Unassiged if I do so?
> Does anyone have an Idea how to deal with unassigned values without
> creating a fact table based just on views from the source?
>
> Thanks in anticipation...
>
> Ajay

can I create a VIEW on the sesult of stored procedure ?

I have a store procedure which select/calculate fields from multiple tables,

can I create a VIEW on the sesult of the stored procedure ?

u can create a view using a select statement containing ur sp converted to a function....
but why would u want to do such a thing....do u want to permanently keep these values..//refer them just then...some more info may get u a better suggestion buddy...|||I want create a cube fact that is base on the result of a stored procedure, but the cube cannot accept SP as a source, so I want to create a view that can be the source of cube fact|||

In this case I think your best bet would probably be to simply create and populate a table with the results of your stored procedure. If you don't need to keep these results then you can truncate and/or drop the table when you don't need it anymore.

I don't think it's possible to create a view based on the output of a SP.

sql

Tuesday, March 20, 2012

Can I copy a DTS Package?

The scenario:
9 db tables populated by 9 Excel Import Files via DTS.
Will I need to create a DTS package for each import? Columns are identical in all 9 - the only thing different is the destination table name and source file name.

I've had to map over 80 columns using DTS and don't want to do it for each instance!

Any help would be appreciated..1. Rightclick the DTS-package in Enterprise Manager
2. Choose "design package"
3. Make the changes you want
4. Go to menuitem "Package" and choose "Save as"
5. You now have a copy of your DTS-package|||Nice one! Cheers.|||This is not the best way -
Dude - create a table in the destination db called tblFileSource that has:
ID, SourceFile, DestinationTable, importDate

Define FileSource and DestinationTable as Global Variables in the package.
Then, the first step of you package use a Execute SQL task that will set you global Varaiables to the result of

select top 1 SourceFile, DestinationTable
from tblFileSource
where importDate is null

Then, use a Dynamic Properties Task to change the source and destination in the Data Transformation Step.

Then, after the Transformation, do another Execute SQL Task (on Success):

Update tblFileSource
Set importDate = getDate()
where SourceFile = ?

Where the ? is the global Variable FileSource.

Isn't this a more professional method - comes in handy when the number of files increases.

can I combine several partitions into one in a cube?

Hi,
I got 4 partitions corresponding to four fact tables.
Does it affect cube query performance? most of time, i need to access all
partitions.
If so, how can I combine them into one partition?
Thanks,
GuangmingIf you are using MOLAP storage (which should be mostly the case) then the
underlying relational fact tables are never touched during a query.
Partitioning is very important for smooth even response times. See the AS
Performance Guide here:
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
when it discusses partitioning. It is also discussed extensively in the SQL
Server 2000 Resource Kit which has an entire chapter on partitioning.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
> Hi,
> I got 4 partitions corresponding to four fact tables.
> Does it affect cube query performance? most of time, i need to access all
> partitions.
> If so, how can I combine them into one partition?
> Thanks,
>
> Guangming|||In management studio, go to cube - partition, right click you'll get 'merge
partition ...'.
it seems that if there are multiple partitions in one measure group, you can
merge them. If partitions are for different measure groups, you can not.
Most time, merging is not necessary, I guess.
Guangming
"Dave Wickert [MSFT]" wrote:

> If you are using MOLAP storage (which should be mostly the case) then the
> underlying relational fact tables are never touched during a query.
> Partitioning is very important for smooth even response times. See the AS
> Performance Guide here:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx[/ur
l]
> when it discusses partitioning. It is also discussed extensively in the SQ
L
> Server 2000 Resource Kit which has an entire chapter on partitioning.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.co
m>
> wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com..
.
>
>|||
> In management studio, go to cube - partition, right click you'll get 'merg
e
> partition ...'.
> it seems that if there are multiple partitions in one measure group, you c
an
> merge them. If partitions are for different measure groups, you can not.
Usually you have different measure groups, because the tables have
different columns. So by definition you have a different structure in
the partition therefore it does not make sense to merge them
If you do have multiple measure groups of identically structured fact
tables then they probably should be setup as multiple partitions under
the on measure group, rather then multiple measure groups each with a
single partition.

> Most time, merging is not necessary, I guess.
In AS2k, the server would read and resolve the data from each partition
using a separate thread so having multiple partitions can give you a
performance boost, especially on a multi processor machine with a fast
disk sub system.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbellsql

can I combine several partitions into one in a cube?

Hi,
I got 4 partitions corresponding to four fact tables.
Does it affect cube query performance? most of time, i need to access all
partitions.
If so, how can I combine them into one partition?
Thanks,
Guangming
If you are using MOLAP storage (which should be mostly the case) then the
underlying relational fact tables are never touched during a query.
Partitioning is very important for smooth even response times. See the AS
Performance Guide here:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
when it discusses partitioning. It is also discussed extensively in the SQL
Server 2000 Resource Kit which has an entire chapter on partitioning.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
> Hi,
> I got 4 partitions corresponding to four fact tables.
> Does it affect cube query performance? most of time, i need to access all
> partitions.
> If so, how can I combine them into one partition?
> Thanks,
>
> Guangming
|||In management studio, go to cube - partition, right click you'll get 'merge
partition ...'.
it seems that if there are multiple partitions in one measure group, you can
merge them. If partitions are for different measure groups, you can not.
Most time, merging is not necessary, I guess.
Guangming
"Dave Wickert [MSFT]" wrote:

> If you are using MOLAP storage (which should be mostly the case) then the
> underlying relational fact tables are never touched during a query.
> Partitioning is very important for smooth even response times. See the AS
> Performance Guide here:
> http://www.microsoft.com/technet/pro.../ansvcspg.mspx
> when it discusses partitioning. It is also discussed extensively in the SQL
> Server 2000 Resource Kit which has an entire chapter on partitioning.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
> wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
>
>
|||
> In management studio, go to cube - partition, right click you'll get 'merge
> partition ...'.
> it seems that if there are multiple partitions in one measure group, you can
> merge them. If partitions are for different measure groups, you can not.
Usually you have different measure groups, because the tables have
different columns. So by definition you have a different structure in
the partition therefore it does not make sense to merge them
If you do have multiple measure groups of identically structured fact
tables then they probably should be setup as multiple partitions under
the on measure group, rather then multiple measure groups each with a
single partition.

> Most time, merging is not necessary, I guess.
In AS2k, the server would read and resolve the data from each partition
using a separate thread so having multiple partitions can give you a
performance boost, especially on a multi processor machine with a fast
disk sub system.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

can I change owner of replicated tables?

can I change the owner of tables that are part of a merge replication
without effecting that replication? or will I need to drop all replication,
change owners, then re-setup replication?
any info is appreciated. Thanks.
no you can't and this will cause major problems.
The best thing to do is to drop your publication, drop the tables on the
subscriber and recreate it.
"djc" <noone@.nowhere.com> wrote in message
news:%23UZcu%23OZEHA.1000@.TK2MSFTNGP12.phx.gbl...
> can I change the owner of tables that are part of a merge replication
> without effecting that replication? or will I need to drop all
replication,
> change owners, then re-setup replication?
> any info is appreciated. Thanks.
>
|||ok. thanks.
Hillary,
are you familiar with the merge repl problems mentioned by Kestutis
Adomavicius in post titled: 'cannot remove repl... cannot delete database'?
If so could you offer some more insight into that and the questions I had in
reply to him?
Thanks... your other posts have helped me out a lot.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OAIF8ZPZEHA.212@.TK2MSFTNGP11.phx.gbl...
> no you can't and this will cause major problems.
> The best thing to do is to drop your publication, drop the tables on the
> subscriber and recreate it.
> "djc" <noone@.nowhere.com> wrote in message
> news:%23UZcu%23OZEHA.1000@.TK2MSFTNGP12.phx.gbl...
> replication,
>
|||I have been able to repro the problem with the conflict tables that Kestutis
has pointed out. You get this error when dropping replication and it is
simple to fix.
Kestutis (quite brilliantly I might add) has found some problems with the
merge replication stored procedures which may or may not cause problems. The
conflict table dropping is definately related to this problem which Kestutis
has fixed.
I have successfully implemented a central publisher with 60 subscribers
using non dbo table owners. This replication solution was relatively
maintenance free for the two years I was working on it. So, it is not a show
stopping problem, but what problems might occur is unknown. What will happen
in a future service pack is also unknown.
I have forwarded Kestutis's fixed replication stored procedures to a contact
of mine at Microsoft - with credit to Kestutis of course. Hopefully
Microsoft will implement Kestutis's suggestions.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"djc" <noone@.nowhere.com> wrote in message
news:%23ZV2u1PZEHA.2456@.TK2MSFTNGP10.phx.gbl...
> ok. thanks.
> Hillary,
> are you familiar with the merge repl problems mentioned by Kestutis
> Adomavicius in post titled: 'cannot remove repl... cannot delete
database'?
> If so could you offer some more insight into that and the questions I had
in
> reply to him?
> Thanks... your other posts have helped me out a lot.
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OAIF8ZPZEHA.212@.TK2MSFTNGP11.phx.gbl...
>

Can I call a web service from SQL?

I have two databases with two identical tables in seperate physical locations. I want database B tables to be updated automatically when database A tables change. Is there a way to call a web service from SQL to make this happen? Or is there a better way to do this? I would really like it to get the rows that were modified and then copy only those rows to the other database tables. If anyone knows if this can be done please let me know. Thank you.

Why do you need a webservice? Maybe it's better to archieve this using UPDATE Triggers? Although the performance of firing triggers is not so good in some case, but it should be easier and faster than calling webservice. So let's say you want to trace modification made in database1.dbo.Table1, and you want to copy the modified rows into database2.dbo.Table2, and the 2 tables have almost the same structure, except the Table2 has 1 more column used to record UPDATETIME. You can create an UPDATE/INSERT trigger on Table1 like this:

CREATE TRIGGER trg_TraceMod ON tempdb.dbo.tbl_Const1 FOR UPDATE
AS
IF(object_id('tempdb.dbo.tbl_Trace') IS NULL)
BEGIN
SELECT deleted.*,GETDATE() AS UpdTime INTO tempdb.dbo.tbl_Trace FROM deleted

END
ELSE
INSERT INTO tempdb.dbo.tbl_Trace
SELECT deleted.*,GETDATE() FROM deleted
go

To learn more about triggers, you can refer to:

Enforcing Business Rules with Triggers

|||Thank you very much for the reply. The reason I was think about a webservice is because the two databases are in two different physical locations and cannot access each other without a web service because they are not on the same network. Basically one database is in our office and the other one in downtown in a data center. The one in the data center needs to update the one in our office everytime it changes. Could I use triggers to do that?|||

Then you can tryLinked Servers. Connections between internet SQL Servers may be more complex than in the same network, there may be trouble in locating host SQL box, passing credentials, firewalls, and so on. You may take a look at this post if you fail to establish connections between the 2 SQL servers:

http://forums.asp.net/thread/1289341.aspx

And after the Linked Servers have been created (you can do this in Enterprise Manager->Security->Linked Servers), you can query the tables on the linked server using four part object name:

server.database.owner_name.object_name

Or you can useOPENQUERY.

|||

My question is somewhat related. I need to call a web service upon completion of a sql job. Is this possible? I'm using a product called Captaris Workflow. What I need to do is call a web service that creates a new workflow process and emails the person responsible for the first workflow task. The workflow part may sound foreign, nevertheless, I want to call a web service when a sql job completes. Any help would be much appreciated.

Thanks,

Jason

can i calculate a database backup size?

Hi,
I've a database with multiple millions of rows in a small set of tables.
Most of this data is historical, so I want to split the data in multiple
databases, make backups of about 600 MB each and burn this to cd roms. I
want to split the data based on dates stored inside the tables. Is there a
way to calculate the amount of bytes inside a database backup file that will
be produced ba a diven select statement when it uses all rows of a table?
SvenBackups aren't caused by select statements. They backup all of the physical
data in the database unless you do file or filegroup backups. So if you
want to get an idea of the size the backup will be you only have to look at
how much actual data (not including free space) there is in the db and it
will be approximately that. You can compress backups quite easily with
WINZIP or similar utilities and fit much more on a cd.
--
Andrew J. Kelly
SQL Server MVP
"Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
news:eJ3BY7XZDHA.1832@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've a database with multiple millions of rows in a small set of tables.
> Most of this data is historical, so I want to split the data in multiple
> databases, make backups of about 600 MB each and burn this to cd roms. I
> want to split the data based on dates stored inside the tables. Is there a
> way to calculate the amount of bytes inside a database backup file that
will
> be produced ba a diven select statement when it uses all rows of a table?
> Sven
>|||Good point Dan. Be sure to run UPDATE USAGE or use the Updateusage option
when running it.
--
Andrew J. Kelly
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23Cs0mbZZDHA.2572@.TK2MSFTNGP12.phx.gbl...
> To add to Andrew's response, the reserved space reported by sp_spaceused
> can provide a rough estimate of database backup space requirements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> news:eJ3BY7XZDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I've a database with multiple millions of rows in a small set of
> tables.
> > Most of this data is historical, so I want to split the data in
> multiple
> > databases, make backups of about 600 MB each and burn this to cd roms.
> I
> > want to split the data based on dates stored inside the tables. Is
> there a
> > way to calculate the amount of bytes inside a database backup file
> that will
> > be produced ba a diven select statement when it uses all rows of a
> table?
> >
> > Sven
> >
> >
>|||Sven,
That's a really awkward way of going about it. Why not just BCP out the
data to flat files (I suggest native mode) and load them onto the CD(s).
You can use normal select statements to break them up into what ever you
want. It will be much cleaner and easier than attempting to do this with
backups. As for the size, it would be difficult to tell exactly unless all
your columns are of the fixed size (no varchars, text etc.). I think with a
few test selects you should be able to get a pretty good idea though.
--
Andrew J. Kelly
SQL Server MVP
"Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
news:u9WCdCkZDHA.2520@.TK2MSFTNGP09.phx.gbl...
> I plan to split up database. 99% of the size of my database is caused by
> "Table1". What I need to know is: How many rows do I have to delete to be
> able to produce a backup os ~600MByte. I need functional database backups
to
> be able to restore data from a specific date.
> The idea was to have a second database with all the data but Table1, then
> - do a move of x rows from the original database to the new database
> - then make a backup
> - truncate Table1 of the backed up database
> - repeat these steps until all data is backed up.
> Now I need to know what type of relationship is there between space used
in
> a database and space used in a backup file. I don't want to split one huge
> database backup file to burn it on cdroms, because then I need to restore
> the complete database from 100 CDs just to lookup one row - that's not
what
> I had in mind ;).
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eQAY7jYZDHA.3444@.tk2msftngp13.phx.gbl...
> > Backups aren't caused by select statements. They backup all of the
> physical
> > data in the database unless you do file or filegroup backups. So if you
> > want to get an idea of the size the backup will be you only have to look
> at
> > how much actual data (not including free space) there is in the db and
it
> > will be approximately that. You can compress backups quite easily with
> > WINZIP or similar utilities and fit much more on a cd.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> > news:eJ3BY7XZDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > >
> > > I've a database with multiple millions of rows in a small set of
tables.
> > > Most of this data is historical, so I want to split the data in
multiple
> > > databases, make backups of about 600 MB each and burn this to cd roms.
I
> > > want to split the data based on dates stored inside the tables. Is
there
> a
> > > way to calculate the amount of bytes inside a database backup file
that
> > will
> > > be produced ba a diven select statement when it uses all rows of a
> table?
> > >
> > > Sven
> > >
> > >
> >
> >
>

Monday, March 19, 2012

Can I avoid temp tables, etc.

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored
procedure to a calling stored procedure? Can we avoid temp tables in
this case?
2. Are operations in a stored procedure are treated as a transaction?

Any help will be greatly appreciated.

Background: We need to use temp table to pass recordsets from a nested
stored procedure to a calling stored procedure. Our understanding is
that in this case, we have no choice but to use temp tables. So, we
need to optimize the performance as much as possible. To do this, we
wanted to find out whether operations in a stored procedure are treated
as a transaction. We are using SQL 2000 SP4. I could not find any
answers so I did the following experiment.

Experiment 1:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS

Update
Articles
SET
IsUpdate = 20
where
ArticlesId < 80000

SELECT * from Articles

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

"SELECT * from Articles" takes a long time (about 40 seconds) to
complete

Before executing the SP, the IsUpdate attribute for all articles is 30.
Then I executed this SP. Before the SP is finished, I end the SP
manually. I checked the IsUpdate attribute again, and found that all
Articles's (ArticlesId < 80000) Isupdate attribute is now 20. The
operations did not rollback. I interpret this to mean that the whole SP
is not treated as a transaction.

Then, I did experiment 2 below. This time, I explicitly declared the
transaction.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Wiz_SP_Transaction_Test]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[Wiz_SP_Transaction_Test]
GO

CREATE PROCEDURE [dbo].[Wiz_SP_Transaction_Test]
AS
BEGIN TRANSACTION
Update
Articles
SET
IsUpdate = 50
where
ArticlesId < 80000

SELECT * from Articles

IF @.@.ERROR <0 ROLLBACK TRANSACTION
COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Before this second SP, the IsUpdate attribute is 20 (set in the first
experiment). I run this second SP and ended it manually before it
finished. I checked the IsUpdate attributes for all Articles's
(ArticlesId < 80000), but their Isupdate attribute is 50. So the
operation did not rollback either. But we have declared the transaction
explicitly. Does this mean that the SP is still not treated as a
transaction?(betbubble@.gmail.com) writes:

Quote:

Originally Posted by

I need help on two questions:
1. Is temp table the only way to pass recordsets from a nested stored
procedure to a calling stored procedure? Can we avoid temp tables in
this case?

No, there are more alternative: use a process-keyed table. As long
as the access is from T-SQL only, @.@.spid works fine. We use this
technique a lot in our shop.

There is also INSERT-EXEC, but I like this less.

I discuss these options in more detail in an article on my web site:
http://www.sommarskog.se/share_data.html

Quote:

Originally Posted by

2. Are operations in a stored procedure are treated as a transaction?

A procedure as such does not define any transaction scope. However,
each INSERT, UPDATE and DELETE statement defines a transaction if
there is no other transaction active. This transaction includs any
trigger that is fired the statement. And in case of INSERT EXEC, the
called procedure will operate in the context of the transaction
defined by the INSERT statement.

Note that this applies, regardless of the INSERT, UPDATE or DELETE
statement appears in a stored procedure or not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What are you using SET ANSI_NULLS OFF for?|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

What are you using SET ANSI_NULLS OFF for?

I would guess that betbubble uses Enterprise Manager to create his
procedures. Which is a very bad idea, for the precise reason Alexander
points out (thanks for catching it!): Enterprise Manager has incorrect
defaults for ANSI_NULLS and QUTOED_IDENTIFIERS. You have rarely reason
to have these options off (least of all ANSI_NULLS), but there are
features in SQL Server that requires these settings to be ON, so by
all means run with them.

If you use Query Analyzer to edit stored procedures, you get the
correct defaults.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland and Alexander,
Thanks a lot. I will experiment with them and report back.|||These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFIERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

I read the article by Erland. Great information! Thanks. I am
experimenting with the Process-Keyed tables, which are very big tables.
I have many querys concurrentlly, they will need to use the same
Process-Keyed tables. Any advice on reducing locks will be appreciated.|||(betbubble@.gmail.com) writes:

Quote:

Originally Posted by

These SPs were generated using the Query Analyzer, ANSI_NULLS and
QUTOED_IDENTIFIERS are OFF. I turned them ON manually. Is there
something wrong with my Query Analyzer settings?

You can change the connection settings under Tools->Options->Cononection
Properties. The default settings is that all settings for indexed views
are on, but you might have changed that at some point.

Also, if the SP was originally created by EM, and you scripted it from
QA, QA will include the original settings in the script, so you will
actively have to change them - or just remove them-

Thursday, March 29, 2012

can i have the same parameter 3 times for different results?

Can I have multiple tables to gather event data

Tuesday, March 27, 2012

Can I have an SQL view like in Access

Can I have an SQL view like in Access

can i grant all privilege to user?

Can I get URL for RS from database ReportServer?

Can I generate Table Script in a Stored Procedure?

Sunday, March 25, 2012

Can I fill a cursor from a strored procedure?

can I export tables so that existing tables in destination database will be modified?

Can I do this in SQL Analyzer or with a dts package?

Thursday, March 22, 2012

Can I create table with constrains in SMO?

Can I create OLAP Cubes without a staging area?

can I create a VIEW on the sesult of stored procedure ?

Tuesday, March 20, 2012

Can I copy a DTS Package?

can I combine several partitions into one in a cube?

can I combine several partitions into one in a cube?

can I change owner of replicated tables?

Can I call a web service from SQL?

can i calculate a database backup size?

Monday, March 19, 2012

Can I avoid temp tables, etc.

Calling Webservice

Blog Archive

About Me