How do I move a web assistant job?

Hi-
I'm bringing up a replacement production SQL 7 server and I've got a few
Web Assistant jobs that create web pages on the old server. I want to
script them out and automatically move them, but they keep failing on the
create web page step. Is there any way to transfer these jobs with out
going through the wizards?
Thanks,
AceIIRC, They should be stored as a job.
Look for the sp_makewebtask job steps.
Rick Sawtell
MCT, MCSD, MCDBA
"Ace McDugan" <alohnsql@.hotmail.com> wrote in message
news:eoSLRkIsEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hi-
> I'm bringing up a replacement production SQL 7 server and I've got a
few
> Web Assistant jobs that create web pages on the old server. I want to
> script them out and automatically move them, but they keep failing on the
> create web page step. Is there any way to transfer these jobs with out
> going through the wizards?
> Thanks,
> Ace
>

How do I move a web assistant job?

Hi-
I'm bringing up a replacement production SQL 7 server and I've got a few
Web Assistant jobs that create web pages on the old server. I want to
script them out and automatically move them, but they keep failing on the
create web page step. Is there any way to transfer these jobs with out
going through the wizards?
Thanks,
AceIIRC, They should be stored as a job.
Look for the sp_makewebtask job steps.
Rick Sawtell
MCT, MCSD, MCDBA
"Ace McDugan" <alohnsql@.hotmail.com> wrote in message
news:eoSLRkIsEHA.1816@.TK2MSFTNGP09.phx.gbl...
> Hi-
> I'm bringing up a replacement production SQL 7 server and I've got a
few
> Web Assistant jobs that create web pages on the old server. I want to
> script them out and automatically move them, but they keep failing on the
> create web page step. Is there any way to transfer these jobs with out
> going through the wizards?
> Thanks,
> Ace
>sql

How do I make a view read-only

I want to create a view that can not be used for record inserts, updates or
deletes - by anyone, including administrators. Is there an option that can
be used in creating the view so I don't have to go through all the users
setting up deny permissions?
http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Data_manipulation6.html
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?
|||There is nothing you can do to deny administrators rights to your view.
Your best bet is to create an INSTEAD OF TRIGGER that just generates an
error message whenever someone tries to modify the view.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?
|||Thank you for all your suggestions. I came up with this solution: I added
DISTINCT to the syntax. Since the select list included the unique
identifier, I get the same view results, but now the view is not editable.
"Bev Kaufman" wrote:

> I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?
|||Keep in mind that this solution might not always possible. The INSTEAD OF
trigger solution is much more general purpose. Also in many cases, adding a
DISTINCT can have a negative impact on query performance if there is no
guaranteed uniqueness. Does your uniqueifier have a unique index? That is
the only way that SQL Server knows the values are already unique and doesn't
add extra processing for the DISTINCT.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:89E10E73-53AD-466B-93F1-7C8B9E6C3E0F@.microsoft.com...[vbcol=seagreen]
> Thank you for all your suggestions. I came up with this solution: I added
> DISTINCT to the syntax. Since the select list included the unique
> identifier, I get the same view results, but now the view is not editable.
> "Bev Kaufman" wrote:

How do I make a view read-only

I want to create a view that can not be used for record inserts, updates or
deletes - by anyone, including administrators. Is there an option that can
be used in creating the view so I don't have to go through all the users
setting up deny permissions?http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Data_manipulation6.html
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||If you don't want to change the view definition you can add an INSTEAD_OF
trigger for INSERT, DELETE and UPDATE that do nothing.
--
Rubén Garrigós
Solid Quality Mentors
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||Hi Bev
I really think this should be covered by the permissions you grant to the
view, trying to stop administrators updating the view would just mean only
mean they would need to hit the base tables instead.
John
"Bev Kaufman" wrote:
> I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||There is nothing you can do to deny administrators rights to your view.
Your best bet is to create an INSTEAD OF TRIGGER that just generates an
error message whenever someone tries to modify the view.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||Thank you for all your suggestions. I came up with this solution: I added
DISTINCT to the syntax. Since the select list included the unique
identifier, I get the same view results, but now the view is not editable.
"Bev Kaufman" wrote:
> I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||Keep in mind that this solution might not always possible. The INSTEAD OF
trigger solution is much more general purpose. Also in many cases, adding a
DISTINCT can have a negative impact on query performance if there is no
guaranteed uniqueness. Does your uniqueifier have a unique index? That is
the only way that SQL Server knows the values are already unique and doesn't
add extra processing for the DISTINCT.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:89E10E73-53AD-466B-93F1-7C8B9E6C3E0F@.microsoft.com...
> Thank you for all your suggestions. I came up with this solution: I added
> DISTINCT to the syntax. Since the select list included the unique
> identifier, I get the same view results, but now the view is not editable.
> "Bev Kaufman" wrote:
>> I want to create a view that can not be used for record inserts, updates
>> or
>> deletes - by anyone, including administrators. Is there an option that
>> can
>> be used in creating the view so I don't have to go through all the users
>> setting up deny permissions?sql

How do I go about placing photographs in SQL?

I have SQL 2005 Server and Visual Studio 2005 and I am working in vb. I want to create a database which has photographs.

Not sure if this is the best place for the question, but check into the VARBINARY(MAX) datatype for image storage.

Simone

|||

I found an image data-type.

thanks...

how do I get the value of a DEFAULT

Hi

We have a default defined in our database

CREATE DEFAULT [ schema_name . ] default_name
AS constant_expression [ ; ]

How can I ge the value of the constant using SQL ?

Also - anyone know why this is going to be removed from a future version of SQL - we use it for partitioning with replicated clients (long story) - but every table has one column which is bound to this default. We find it *very* handy.

Ta
Bruce

take a look at the sys.default_constraints catalog view, the definition column will have the value

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||We are recommending that you use default constraints instead of DEFAULTs. Would that not work for you?|||

If we were starting the project now, then yes, but we have many installations around the globe relying on this feature....

Bruce

|||

That shows the default constraints, but not our DEFAULT (terminology so similar but different - I'm confused!)

thanks
bruce

|||

sp_helpconstraint tablename

Adamus

|||

Thats closer, but I really just want to know that the integer value of my DEFAULT is ...

I see it joins on sys.columns and syscomments to get a descriptive string of the default - I was hoping for something a little more direct - in the meantime I'll write a function to work it out..

thanks

|||

syscomments is deprecated in sql server 2005. Please use sys.sql_modules instead or you can use the below built-in object_definition.

select object_definition(default_object_id) from sys.columns where default_object_id <> 0

|||

SELECT d.* FROM sys.default_constraints as d
JOIN sys.objects as o
ON o.object_id = d.parent_object_id
JOIN sys.columns as c
ON c.object_id = o.object_id AND c.column_id = d.parent_column_id
JOIN sys.schemas as s
ON s.schema_id = o.schema_id
WHERE o.name='<TableName>' AND c.name = '<ColumnName>'

The above query will show the default constraint properties of the column specified in the above query.

The default value can be obtained from the column d.[definition] in the above query.

Thanks,
Loonysan

sql

How do I get the script to create a table & all the Indexes

In SS2K I could go to task and it would script the table, indexes and any other script that was set for that table. How do I do it with 2005?

Thanks

Have you looked at the Generate Script Wizard?|||

No. How do you use it?

Thanks

|||Right click on a database, select all tasks, generate scripts and then just follow the steps. If you are using SSMS Express it is not included.|||

That works for me.

Thanks

|||? You might want to check out this tool: http://www.sqlteam.com/publish/scriptio/ -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <VonSch@.discussions.microsoft.com> wrote in message news:102a22da-2ee1-4208-a1b1-79dfe66a70a6@.discussions.microsoft.com... In SS2K I could go to task and it would script the table, indexes and any other script that was set for that table. How do I do it with 2005? Thanks|||

I tried it and it works.

Thanks.

how do i get the returned id from the store proceedure in asp c#?

my store proceedure gets the id:

CREATE PROCEDURE createpost(
@.userID integer,
@.categoryID integer,
@.title varchar(100),
@.newsdate datetime,
@.story varchar(250),
@.wordcount int
)
as
DECLARE @.newNewsID integer

Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)

SELECT @.newNewsID = @.@.IDENTITY

then im calling it in the asp:

con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();

cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;

cmdselect.Parameters.Add("@.userID", userID);
cmdselect.Parameters.Add("@.categoryID", categoryID );
cmdselect.Parameters.Add("@.title", title.Text );
cmdselect.Parameters.Add("@.newsdate", newsdate.Text );
cmdselect.Parameters.Add("@.story", story.Text );
cmdselect.Parameters.Add("@.wordcount", "1" );


int valueinserted = cmdselect.ExecuteNonQuery();


Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

con.Close();

as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?

Hi,

you need to either specify a RETURN or make the @.newNewsID an output parameter.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

|||

Or use the SqlDataReader object like this:

1CREATE PROCEDURE createpost(2 @.userID integer,3 @.categoryID integer,4 @.titlevarchar(100),5 @.newsdatedatetime,6 @.storyvarchar(250),7 @.wordcountint8)9as1011Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)12Values (@.userID, @.categoryID, @.title, @.newsdate, @.story, @.wordcount)1314SELECT@.@.IDENTITY

and in  code:2con =new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");3int valueinserted = 0;45try6 cmdselect =new SqlCommand("createpost", con);7 cmdselect.CommandType = CommandType.StoredProcedure;89 cmdselect.Parameters.Add("@.userID", userID);10 cmdselect.Parameters.Add("@.categoryID", categoryID );11 cmdselect.Parameters.Add("@.title", title.Text );12 cmdselect.Parameters.Add("@.newsdate", newsdate.Text );13 cmdselect.Parameters.Add("@.story", story.Text );14 cmdselect.Parameters.Add("@.wordcount","1" );1516 cmdselect.Connection.Open();17 SqlDataReader reader = command.ExecuteReader();18if( reader.HasRows() )19 {20 reader.Read();21 valueinserted = Convert.ToInt32( reader[0] );22 }23catch( Exception ex)24{25//handle the exception somehow26}27finally28{29if( con !=null and conn.State != ConnectionState.Closed )30 con.Close();31}3233Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);

sql

How do I get the most recent data from a table?

I'm trying to create a stored procedure from a join of two tables. One table holds a list of containers, and the other table holds the history of the contents of those containers. All I want is to retrive the most recent history for each container. For example, the containers table has the container number and name, and the history table has the

volume in the container and the date and time of the measurements. There can be any number of measurements, but I only want the most recent one.

Normally, I would just create a cursor that holds a list of the containers and some blank fields, and then loop through it, retrieving the most recent record one by one, but I don't know how to do that in Transact-SQL. Also, I thought maybe some SQL wizard out there might know of a way to do it with a simple select statement.

Geoffrey Callaghan

In 2005, the easiest way is to use the ROW_NUMBER() function:

create table container
(
containerId int primary key,
name varchar(10)
)
create table containerHistory
(
containerId int references container(containerId),
containerHistoryDate datetime,
value numeric(4,2),
primary key (containerId, containerHistoryDate)
)
insert into container
select 1,'Fred'
union all
select 2,'Barney'

insert into containerHistory
select 1,'20070101',1.1
union all
select 1,'20070102',1.12
union all
select 1,'20070103',1.1
union all
select 1,'20070104',1.8
union all
select 2,'20070101',1.1

select container.containerId, container.name, containerHistory.value
from container
join (select containerId,
row_number() over (partition by containerId order by containerHistoryDate desc) as rowNum,
value
from containerHistory) as containerHistory
on container.containerId = containerHistory.containerId
and containerHistory.rowNum = 1

Getting the first one (ordered decending) will get you the last one.

|||

Actually, I found an easier way to do it that seems to work.

select container.number, container.name,

(select top 1 qty_meas+qty_added from containerHistory where container.number = containerHistory .container_nbr

order by datetime desc) as balance,

(select top 1 datetime from containerHistory where tank.number = containerHistory .container_nbr

order by datetime desc) as LastReading

from container

This works well and runs fast. Do you see any problems with it?

|||

No, if that works for you, it may be faster/better. It really depends on how many of those subqueries you will need. The Row_number solution is really good for making sure that you get an entire row from a table. However, you want to get a single value from 2 different tables, well your way is probably best.

The row_number trick is going to be the best way to get the last (or first) full row in a set of rows.

How do I get the most recent data from a table?

I'm trying to create a stored procedure from a join of two tables. One table holds a list of containers, and the other table holds the history of the contents of those containers. All I want is to retrive the most recent history for each container. For example, the containers table has the container number and name, and the history table has the

volume in the container and the date and time of the measurements. There can be any number of measurements, but I only want the most recent one.

Normally, I would just create a cursor that holds a list of the containers and some blank fields, and then loop through it, retrieving the most recent record one by one, but I don't know how to do that in Transact-SQL. Also, I thought maybe some SQL wizard out there might know of a way to do it with a simple select statement.

Geoffrey Callaghan

In 2005, the easiest way is to use the ROW_NUMBER() function:

create table container
(
containerId int primary key,
name varchar(10)
)
create table containerHistory
(
containerId int references container(containerId),
containerHistoryDate datetime,
value numeric(4,2),
primary key (containerId, containerHistoryDate)
)
insert into container
select 1,'Fred'
union all
select 2,'Barney'

insert into containerHistory
select 1,'20070101',1.1
union all
select 1,'20070102',1.12
union all
select 1,'20070103',1.1
union all
select 1,'20070104',1.8
union all
select 2,'20070101',1.1

select container.containerId, container.name, containerHistory.value
from container
join (select containerId,
row_number() over (partition by containerId order by containerHistoryDate desc) as rowNum,
value
from containerHistory) as containerHistory
on container.containerId = containerHistory.containerId
and containerHistory.rowNum = 1

Getting the first one (ordered decending) will get you the last one.

|||

Actually, I found an easier way to do it that seems to work.

select container.number, container.name,

(select top 1 qty_meas+qty_added from containerHistory where container.number = containerHistory .container_nbr

order by datetime desc) as balance,

(select top 1 datetime from containerHistory where tank.number = containerHistory .container_nbr

order by datetime desc) as LastReading

from container

This works well and runs fast. Do you see any problems with it?

|||

No, if that works for you, it may be faster/better. It really depends on how many of those subqueries you will need. The Row_number solution is really good for making sure that you get an entire row from a table. However, you want to get a single value from 2 different tables, well your way is probably best.

The row_number trick is going to be the best way to get the last (or first) full row in a set of rows.

How do i get the database that i am using in visual studio into my SQL server management s

How do i get the database that i am using in visual studio into my SQL server management studio?

i need to create some scripts to create stored procedures on a live server.

Take your .mdf file for the database that you are using in Visual Studio.NET and attach it to a database in MS SQL Server.

Good luck.

How do I get SSIS to do this...?

I feel like I'm losing my mind. I can write code in 7 different languages, but I can't figure out how to create an SSIS package to do the following:

Table A, 6 columns: EmpID, Code1, Code2, Code3, LocationCode, ScriptPath

Table B, 5 columns: Code1, Code2, Code3, LocationCode, ScriptPath

Using the values in the Code columns for lookup, I need to pull the most specific value from Table B and store it in Table A. So the task is to fill in the LocationCode and ScriptPath columns in Table A from Table B using the following logic:

If

Table B has a row where Code1,

Code2 and Code3 all match the

appropriate values in the Table A

row then copy the LocationCode

from Table B to that row in Table A.

Else If

Table B has a row where Code1

and Code2 both match the appropriate

values in the Table A row then copy

the LocationCode from Table B to

that row in Table A.

Else If

Table B has a row where Code1

matches the appropriate value

Table A row then copy the

LocationCode from Table B to

that row in Table A.

Else

Place a default value in that row

in Table A.

Same logic for the ScriptPath.

The logic is pretty straight forward, but I can't wrap my brain around how to turn this into an SSIS package.

Can someone give me a quick sketch of what components to use to create something like this?

Thanks.

J

Roughly,

Lookup on 1, 2, 3

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1, 2

->Successful lookup -> Union All

->Failed lookup -> Lookup on 1

->Successful lookup -> Union All

->Failed lookup -> Derived Column (for default) -> Union All

It's the same Union All for all branches.

Honestly, I think I'd do this in a Exec SQL, and not a data flow. If I am understanding your question properly, you will be updating Table A, so you'd either have to use an OLE DB Command in the data flow to issue the update, or write it to a temp table and issue an Exec SQL after the data flow.

|||

I'm writing it as a stored procedure now, but I just wanted to get my hands dirty with the SSIS stuff - so I gave it a shot.

I can't even figure out how to get the lookups to work properly in SSIS. When I play with it some more, I'll post the error that I'm getting. Maybe you can tell me what I'm doing wrong at that point...

Thanks for the response.

J

How do I get a user's domain?

I need to provide a UI to get the information to add a windows login to a SqlServer database. The CREATE LOGIN Sql statment requires the user name as "DomainName\UserName". I can get a list of users in XML using the following code:

public static XmlDocument GetAllADDomainUsers(string DomainPath)
{
string domain;
XmlDocument doc = new XmlDocument();
doc.LoadXml("<users/>");
XmlElement elem;

DirectoryEntry searchRoot;

ArrayList allUsers = new ArrayList();

if (DomainPath.Length == 0)
{
DirectoryEntry entryRoot = new DirectoryEntry("LDAP://RootDSE");
domain = entryRoot.Properties["defaultNamingContext"][0].ToString();
}
else
domain = DomainPath;

searchRoot = new DirectoryEntry("LDAP://" + domain);

DirectorySearcher search = new DirectorySearcher(searchRoot);
search.Filter = "(&(objectClass=user)(objectCategory=person))";
search.PropertiesToLoad.Add("samaccountname");
search.PropertiesToLoad.Add("distinguishedname");
search.Sort.PropertyName = "samaccountname";
search.Sort.Direction = SortDirection.Ascending;

SearchResult result;
SearchResultCollection resultCol = search.FindAll();
if (resultCol != null)
{
for(int counter=0; counter < resultCol.Count; counter++)
{
result = resultCol[counter];
if (result.Properties.Contains("samaccountname"))
{
elem = doc.CreateElement("user");
doc.DocumentElement.AppendChild(elem);
elem.SetAttribute("name", (String)result.Properties["samaccountname"][0]);
elem.SetAttribute("distinguishedName", (String)result.Properties["distinguishedname"][0]);
}
}
}
return doc;
}

This works for listing the names but how do I get the NetBIOS domain name for a selected user as required by SqlServer? I have tried using TranslateName from secur32.dll. That works on some machines but for some reason on other machines, it returns a blank. Is there another way?

Thanks for your help,
Rob

Is this the right one you are looking for? Try System.Environment.UserDomainName .|||

System.Environment.UserDomainName gets the domain of the current user. However, I need to be able to get the domain of a user that could come from any of multiple domains instead of the current user and I also need to support version 1.1 of .Net Framwork which makes it more difficult...unless I'm missing something.

Any ideas?

Thanks,
Rob

How do i get a list of all members from a certain attribute hierarchy ?

I'm trying to create a script that extracts all the members from a certain attribute hierachy. I can get down to the single attribute hierachy, but how will i get it's members ?

This is the code from the script that connects to Adwenture Works.

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim sASServer As String = Me.Variables.ASServer.ToString()

Dim oASServer As New Microsoft.AnalysisServices.Server

oASServer.Connect(sASServer)

Dim oASDatabase As New Microsoft.AnalysisServices.Database

Dim oASDim As New Microsoft.AnalysisServices.Dimension

Dim oASDimat As New Microsoft.AnalysisServices.DimensionAttribute

For Each oASDatabase In oASServer.Databases

If oASDatabase.Name = "Adventure Works DW" Then

For Each oASDim In oASDatabase.Dimensions

If oASDim.Name = "Product" Then

For Each oASDimat In oASDim.Attributes

If oASDimat.Name = "Model Name" Then

With asinfoBuffer

.AddRow()

.Database = oASDatabase.ID

.DimID = oASDim.ID

.Dimatt = oASDimat.Name

End With

End If

Next

End If

Next

Else

End If

Next

End Sub

End Class

Wouldn't it be easier to query the cube for the members of the attribute hierarchy, using MDX ?

Best regards

- Jens

How do i get a list of all members from a certain attribute hierarchy ?

I'm trying to create a script that extracts all the members from a certain attribute hierachy. I can get down to the single attribute hierachy, but how will i get it's members ?

This is the code from the script that connects to Adwenture Works.

Code Snippet

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()

Dim sASServer As String = Me.Variables.ASServer.ToString()

Dim oASServer As New Microsoft.AnalysisServices.Server

oASServer.Connect(sASServer)

Dim oASDatabase As New Microsoft.AnalysisServices.Database

Dim oASDim As New Microsoft.AnalysisServices.Dimension

Dim oASDimat As New Microsoft.AnalysisServices.DimensionAttribute

For Each oASDatabase In oASServer.Databases

If oASDatabase.Name = "Adventure Works DW" Then

For Each oASDim In oASDatabase.Dimensions

If oASDim.Name = "Product" Then

For Each oASDimat In oASDim.Attributes

If oASDimat.Name = "Model Name" Then

With asinfoBuffer

.AddRow()

.Database = oASDatabase.ID

.DimID = oASDim.ID

.Dimatt = oASDimat.Name

End With

End If

Next

End If

Next

Else

End If

Next

End Sub

End Class

Wouldn't it be easier to query the cube for the members of the attribute hierarchy, using MDX ?

Best regards

- Jens

How do I find out how full my files are?

When you create data files and filegroups, you specify a size. My
question is -- how do you know how full they are? Is there a stored
procedure for that?caseahr (caseahr@.gmail.com) writes:

Quote:

Originally Posted by

When you create data files and filegroups, you specify a size. My
question is -- how do you know how full they are? Is there a stored
procedure for that?

It should be possible to find by querying a couple fo system tables.
But before I go ahead, I need to know which version of SQL Server you
are using, because the solution for SQL 2005 is completely different
than for previous versions.

--
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|||Hi,

You may try the following commands :
sp_spaceused : gets the statistics of usage for a DB or a table. these
stats are not splitted by file.
DBCC SHOWFILESTATS : Gets the statistics of usage per data file. Log
file is ignored. The size is given in extents, depending on your system
(usually, the factor number is 64 to get the size in KB).
DBCC SQLPERF (LOGSPACE) : Gets the statistics of usage for the log
file.
sp_helpdb and sp_helpfile : gets the information about size and growth,
for the database and the files.

These functions work properly either on SQL Server 2000 and SQL Server
2005.

Hope this will fit your needs.

Cdric Del Nibbio
MCP since 2003
MCAD .NET
MCTS SQL Server 2005

caseahr a crit :

Quote:

Originally Posted by

When you create data files and filegroups, you specify a size. My
question is -- how do you know how full they are? Is there a stored
procedure for that?

|||Merci beaucoup, Cdric. That's exactly what I needed.

Cdric Del Nibbio wrote:

Quote:

Originally Posted by

Hi,
>
You may try the following commands :
sp_spaceused : gets the statistics of usage for a DB or a table. these
stats are not splitted by file.
DBCC SHOWFILESTATS : Gets the statistics of usage per data file. Log
file is ignored. The size is given in extents, depending on your system
(usually, the factor number is 64 to get the size in KB).
DBCC SQLPERF (LOGSPACE) : Gets the statistics of usage for the log
file.
sp_helpdb and sp_helpfile : gets the information about size and growth,
for the database and the files.
>
These functions work properly either on SQL Server 2000 and SQL Server
2005.
>
Hope this will fit your needs.
>
Cdric Del Nibbio
MCP since 2003
MCAD .NET
MCTS SQL Server 2005
>
>
caseahr a crit :
>

Quote:

Originally Posted by

When you create data files and filegroups, you specify a size. My
question is -- how do you know how full they are? Is there a stored
procedure for that?

How do I find I am administrator?

Thanks Hari.
Since I am db_owner, why I am not able to create new store procedure, view
and table? It gives me error "CREATE TABLE permission denied in database
'myTestDB'.
Your suggestion would be great help.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi Sunny,
> 1.What is that means. I do not have admin rights to whole server?
> Based on the output you fell inside the 'db_owner' role in your database.
> This ensure that you can do any activiites inside that database.
> But you not in the part of 'SYSADMIN' role, which is the server wide
role.
> Due to that your SP_HELPLOGINS failed. This command will list the details
of
> all Logins who can access SQL server.
> 2.What is that means. I do not have admin rights to whole server?
> Yes, You have full access to only ur database.
> 3.Is package admin is different than server adminn?
> Yes, While saving the package you can mention a Owner password. That might
> be needed while accessing the exiting package.
> Thanks
> Hari
> MCDBA
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
am
> SQL
execute
does
knowwing
good
of
> installed
there
> manager
>
Hi Sunny,
It seems, the Administrator has denied access to "Create table" , "Create
Procedure" and "Create View" for your SQL server user using the
DENY statement;
deny create table to <user>
go
deny create procedure to <user>
go
deny create view to <user>
In this case even if you are db_owner for a database you will not able to do
Create table / Create View or Create Procedure.
If required ask your administrator to Grant back those previlages using
GRANT statement.
The below previlages can be denied from a DB_OWNER by administartor;
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:eiDTikBHEHA.324@.tk2msftngp13.phx.gbl...
> Thanks Hari.
> Since I am db_owner, why I am not able to create new store procedure, view
> and table? It gives me error "CREATE TABLE permission denied in database
> 'myTestDB'.
> Your suggestion would be great help.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
database.
> role.
details
> of
might
I
> am
access
> execute
> does
> knowwing
> good
> of
in
> there
>

How do I escape ampersands in stored procedure?

I am using the following stored procedure to gather information from one of my tables:
CREATE PROCEDURE usr_GetCustomerNumber
@.custName varchar(20)
AS
SELECT CUNO FROM CIPNAME0
WHERE CUNM LIKE @.custName
GO
The problem I am facing is that some of the customers have ampersands (&) in their names, i.e. A & M Auto Supply. When I feed in anything with an ampersand to @.custName, procedure doesn't return any information. I am using the following code to call the stored procedure:
Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
Dim custSelected As String
Dim custNum As String
Dim objDataSet As New DataSet
Dim objCmd As New SqlCommand("usr_GetCustomerNumber", objConn)
Dim objReader As SqlDataReader
Try
lblStatus.Text = ""
custSelected = lstResults.SelectedItem.Text
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@.custName", custSelected)
objConn.Open()
objReader = objCmd.ExecuteReader
While objReader.Read
custNum = objReader("CUNO")
End While
objReader.Close()
objConn.Close()
Catch ex As SqlException
lblStatus.ForeColor = Color.Red
lblStatus.Text = ex.Message
End Try
lblStatus.Text = custNum
End Sub
I am trying to determine exactly how I would escape the & and still get the desired results. Should I search the custSelected string for the & and try to escape it before it gets to the stored procedure? I have no clue.
Thanks!
Ariston Collander
ariston@.coxcomputer.comAmpersand character does not have any special meaning in LIKE pattern. Only characters %, _, [, ], ^ has special meaning. Your C# code looks find to me. The ampersand character might be getting encoded before reaching your click routine. You can verify it by either setting a break point in the C# code and looking at the value or running a SQL Profiler trace to see the executed SP call with the parameter values.

How Do I embed a RegEx in a Report Model

I am having trouble figuring out how to create a new expression-based field in a report model that relies upon the result of a regular expression. It looks like I cannot make calls to static methods in the .NET in a report model. Correct?

Here is my attempt at the expression I want:
=IF((System.Text.RegularExpressions.Regex.IsMatch(PreferedEmail)),True,False)

The error returned when I attempt to save the expression in Report Model Designer is "The following is character is not valid: ."

BTW, the message is copied verbatim. The poor grammer is not my fault.
I take it that no news is very bad news on this front. There is no way to reference a static .NET method/object in a Report Model expression. That's a real shame.
|||

Kevin,

You can use regular expression in reporting services for example:

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

Hammer

|||I'm going to go out on a limb here & guess that you've never tried that in a Report Model (SDML). You absolutely can do that in an expression embedded in a Report Definition (RDL). But the Report Model Designer will not allow you to save the expression.|||

You are correct -- referencing .NET methods from a report model expression is not supported. Depending on the report the user creates, report model expressions can potentially end up translated into SQL or MDX and embedded deep in some database query.

If you have VS and you're just using the expression as a surface expression in a particular report, you might save your report out as a file, load it up in Report Designer, and then add the expression there. I realize this doesn't give you anything in the model, however.

|||It's not the answer I wanted but now I understand why I can't have what I want. Thanks for the explanation.

How Do I embed a RegEx in a Report Model

I am having trouble figuring out how to create a new expression-based field in a report model that relies upon the result of a regular expression. It looks like I cannot make calls to static methods in the .NET in a report model. Correct?

Here is my attempt at the expression I want:
=IF((System.Text.RegularExpressions.Regex.IsMatch(PreferedEmail)),True,False)

The error returned when I attempt to save the expression in Report Model Designer is "The following is character is not valid: ."

BTW, the message is copied verbatim. The poor grammer is not my fault.I take it that no news is very bad news on this front. There is no way to reference a static .NET method/object in a Report Model expression. That's a real shame.|||

Kevin,

You can use regular expression in reporting services for example:

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

Hammer

|||I'm going to go out on a limb here & guess that you've never tried that in a Report Model (SDML). You absolutely can do that in an expression embedded in a Report Definition (RDL). But the Report Model Designer will not allow you to save the expression.|||

You are correct -- referencing .NET methods from a report model expression is not supported. Depending on the report the user creates, report model expressions can potentially end up translated into SQL or MDX and embedded deep in some database query.

If you have VS and you're just using the expression as a surface expression in a particular report, you might save your report out as a file, load it up in Report Designer, and then add the expression there. I realize this doesn't give you anything in the model, however.

|||It's not the answer I wanted but now I understand why I can't have what I want. Thanks for the explanation.