Hello,
I am trying to do the following:
I have been given an MS Access Database that has a table with columns
I have to create a spreadsheet that will have the data stored in the column header as a row (essentially we are creating a spreadsheet that records all of the different columns in all of the different tables in the MS Access DB).
Any suggestions?
Where the problem is?I have created a login and a user on a database using SMO. I am having trouble mapping a database to the login. When you create a login from the GUI using SQL Server Management Studio Express CTP the user is automatically created and the database is mapped to this login from the user mapping tab. When I create the login programmatically this is not the case so I need to create the user. How do I map the database to the login. I set the default database property on the login but this does not do it.
Thanks,
Michael Gustafson
This is the pattern you would normally follow (this is for a windows user, but it works the same for a SQL user):
Login l = new Login(svr, @."REDMOND\mwories");
l.LoginType = LoginType.WindowsUser;
l.Create();Database db = svr.Databases["AdventureWorks"];
User u = new User(db, @."REDMOND\mwories");
u.Login = l.Name;
u.UserType = UserType.SqlLogin;
u.Create();
This maps the database user to the login as you create the login.
|||
what property of the Login is used to show what databases are mapped?
also is there a way with SMO to check and see if a password is blank? Granted the odds of that are extemely slimmer with 2005.
|||I found the DatabaseMapping() class and was able to find which logins were missing mappings. Im still at a lost at how to test for blank passwords using SQL2005 and SMO.
Thanks for taking time to read this.
Chuck Turner
I have created a login and a user on a database using SMO. I am having trouble mapping a database to the login. When you create a login from the GUI using SQL Server Management Studio Express CTP the user is automatically created and the database is mapped to this login from the user mapping tab. When I create the login programmatically this is not the case so I need to create the user. How do I map the database to the login. I set the default database property on the login but this does not do it.
Thanks,
Michael Gustafson
This is the pattern you would normally follow (this is for a windows user, but it works the same for a SQL user):
Login l = new Login(svr, @."REDMOND\mwories");
l.LoginType = LoginType.WindowsUser;
l.Create();Database db = svr.Databases["AdventureWorks"];
User u = new User(db, @."REDMOND\mwories");
u.Login = l.Name;
u.UserType = UserType.SqlLogin;
u.Create();
This maps the database user to the login as you create the login.
|||
what property of the Login is used to show what databases are mapped?
also is there a way with SMO to check and see if a password is blank? Granted the odds of that are extemely slimmer with 2005.
|||I found the DatabaseMapping() class and was able to find which logins were missing mappings. Im still at a lost at how to test for blank passwords using SQL2005 and SMO.
Thanks for taking time to read this.
Chuck Turner
tblClients ClientID (pk)
tblOrders OrderID (pk) ClientID (fk)
tblOrderPayments PaymentID (pk) OrderID (fk)
Thanks for any help,Depends on how you would like the output. Also: In your tables there's no date. And is not possible for one payment to apply to many orders?
And before I start: You do not 'ask SQL questions', rather you 'construct SQL queries'. And lastly: Always use singular names for your tables, e.g. Client and not Clients, since all tables can hold multiple records all of your table names end up being plural and so the plural becomes redundant.
Anyway. I'll use some common sense to fill in the gaps in your question: I'll assume the payment date rather than the order date is the important one, and I'll assume therefore there's a date on the payment table. Also I'll assume for a moment that your DB design is correct although I doubt it, since it's almost certainly the case that several orders can be paid with one order. I do not know of businesses that does not allow you to pay off several orders in one payment.
So your question as stated is like follows: You want a list of all clients that's made at least one payment a year for the last X years. Is that right? Sounds like a weird question for a business to ask...
If this is indeed what they want, it's a rather interesting SQL problem. I'll give you a few solutions just for my own amusement, even though I suspect they'll be useless for you because your DB design is wrong and you probably misunderstood the needs of your management.
Here's my first attempt:
set @.EndYear = 2004select CustomerID,
CustomerName
from Customer C
where CustomerID in (select distinct CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and datepart(year, P.Date) = @.EndYear)
and CustomerID in (select distinct CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and datepart(year, P.Date) = @.EndYear - 1)
and CustomerID in (select distinct CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and datepart(year, P.Date) = @.EndYear - 2)
Here's a slightly better one:
set @.EndYear = 2004select CustomerID,
CustomerName
from Customer C
join (select distinct CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and datepart(year, P.Date) = @.EndYear) as Y1
on C.CustomerID = Y1.CustomerID
join (select distinct CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and datepart(year, P.Date) = @.EndYear - 1) as Y2
on C.CustomerID = Y2.CustomerID
join (select distinct CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and datepart(year, P.Date) = @.EndYear - 2) as Y3
on C.CustomerID = Y2.CustomerID
set @.EndYear = 2004
set @.NoYears = 4select CustomerID,
CustomerName
from Customer C
join (select CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and datepart(year, P.Date) = @.EndYear) as Y1
on C.CustomerID = Y1.CustomerID
join (select CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and (datepart(year, P.Date) = @.EndYear - 1
or @.NoYears < 2)) as Y2
on C.CustomerID = Y2.CustomerID
join (select CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and (datepart(year, P.Date) = @.EndYear - 2
or @.NoYears < 3)) as Y3
on C.CustomerID = Y3.CustomerID
join (select CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and (datepart(year, P.Date) = @.EndYear - 3
or @.NoYears < 4)) as Y4
on C.CustomerID = Y4.CustomerID
join (select CustomerID
from Order O
join Payment P
on O.OrderID = P.OrderID
and (datepart(year, P.Date) = @.EndYear - 4
or @.NoYears < 5)) as Y5
on C.CustomerID = Y5.CustomerID
Ok, enough of that. Now let's try another approach: Instead of doing it all in one SQL statement, we'll create a temp table with all CustomerIDs in it. Then we'll iterate through the years we want to check, and remove all customers that did not make a payment in each year. So in the end we're left with only the customers that made payments in all the years checked.
set @.EndYear = 2004
set @.NoYears = 4
set @.I = 0select CustomerID
into #Temp
from Customerwhile (@.I < @.NoYears)
begindelete T
from Order O
join Payment P
on O.OrderID = P.OrderID
and datepart(year, P.Date) = @.EndYear - @.I
right join #Temp T
on O.CustomerID = T.CustomerID
where P.OrderID is nullset @.I = @.I + 1
end
select C.CustomerID,
C.CustomerName
from Customer C
join #Temp T
on C.CustomerID = T.CustomerID
I'm going to stop here, but there are almost certainly more elegant solutions out there. I can imagine one where you use a helper table for all the years you want to check, and then do a distinct and a having count on a simple join in a sub-query - it will work in a single query and will be able to do an arbitraty number of years. I'll challenge anyone else out there to give it a shot and I'll post it in a day or two.|||Hi i think i found a nice solution for you :).
Data: i used the Northwind database for testing, i created a payments table containing customerid, orderid and paymentdate and populated it with testdata using the orders table.
DECLARE @.YearSpan int
SET @.YearSpan = 3SELECT
YearsOfPayment.CustomerID,
Sum(FirstYear) As FirstYearPayments,
Sum(SecondYear) As SecondYearPayments,
Sum(ThirdYear) As ThirdYearPayments,
Sum(FourthYear) As FourthYearPayments,
Sum(FifthYear) As FifthYearPaymentsFROM
(
Select
Customers.CustomerID,
(
Case When Payments.Paymentdate Between '01/01/1996' And '12/31/1996' Then 1 Else 0 End
) AS FirstYear,
(
Case When Payments.Paymentdate Between '01/01/1997' And '12/31/1997' Then 1 Else 0 End
) AS SecondYear,
(
Case When Payments.Paymentdate Between '01/01/1998' And '12/31/1998' Then 1 Else 0 End
) AS ThirdYear,
(
Case When Payments.Paymentdate Between '01/01/1999' And '12/31/1999' Then 1 Else 0 End
) AS FourthYear,
(
Case When Payments.Paymentdate Between '01/01/2000' And '12/31/2000' Then 1 Else 0 End
) AS FifthYear
From
Customers
Inner Join Payments On Payments.CustomerID = Customers.CustomerID
) AS YearsOfPaymentGROUP BY
YearsOfPayment.CustomerIDHAVING
SUM(FirstYear) > 0
AND SUM(SecondYear) > 0
AND SUM(ThirdYear) > 0
AND SUM(FourthYear) > (CASE WHEN @.YearSpan > 3 THEN 0 ELSE -1 END)
AND SUM(FifthYear) > (CASE WHEN @.YearSpan > 4 THEN 0 ELSE -1 END)
Todo:
You have to replace the hardcoded dates with a @.FirstYear Parameter and modify the WHEN
clauses accordingly ;)
Good things here:
- if you need to check for more years, just modify it to return an additional CASE column
- performance won't change with the number of years checked (= the number of CASES you return since no subselects are involved
Further Tuning:
- to speed it up a even little more you can create and index on the paymentdate and put a WHERE statement in the "inner" SELECT to only check the years between @.firstdate and @.firstdate + @.yearspan
- Moon|||:: And before I start: You do not 'ask SQL questions', rather you 'construct SQL queries'.
... and before you post again, you should rethink your arrogant attidue and stop correcting someone since most people will find this very offensive.
:: And lastly: Always use singular names for your tables, e.g. Client and not Clients,
:: since all tables can hold multiple records all of your table names end up being plural
:: and so the plural becomes redundant.
... from OO point of view where you think of collections and items, a table is a collection and holding items (records) and therefore it is 100% valid to use plural.|||Moon's solution is novel, but only works for up to a certain number of years. Here's my solution that will work for an arbitrary number of years, with only one query and no temp tables or cursors:
set @.LastYear = 2004
set @.NumYears = 5select D.CustomerId,
C.[Name]
from Customer C
join (select distinct O.CustomerId,
datepart(year, P.[Date]) as PaidYear
from [Order] O
join Payment P
on O.OrderId = P.OrderId
and datepart(year, P.[Date]) <= @.LastYear
and datepart(year, P.[Date]) > @.LastYear - @.NumYears) as D
on C.CustomerID = D.CustomerID
group by D.CustomerID,
C.[Name]
having count(*) = @.NumYears
Who's your daddy! $5 for anyone with a more elegant solution.
And if I come off as arrogant, feel free to skip my comments and just look at my code.|||Relational databases are NOT OO and therefore your plural argument does not hold.
From IDEF1X
December 21, 1993
Section: 3.1.2 Entity Syntax
...The entity name is a noun phrase that describes the set of things the entity
represents. The noun phrase is in singular form, not plural. Abbreviations and
acronyms are permitted, however, the entity name must be meaningful and consistent
throughout the model...
IE and other data modeling standards organisations agree with the above statement. Also I was taught the same thing at University in my first year. I was under the impression that this is common knowledge.|||*points at the year, cough, :P*
well as long as Microsoft uses the plural convention in their own databases: master, Northwind, Pubs, ... i imply that there is a point in using plural for his own database objects as well ;)
Also i didn't say that using singular is a complete NoNo, just that using plural is not forbidden but valid to use.
PS: University != real life ;). I also was taught a few things in university such as C++ that are invalid by now - since when i learned C++, it was still a draft, without namespaces, and whatnot and if i would need to code in C++ today it would be a nearly a full restart.|||Yeah this one is nice :)
And sorry my yesterdays comment was rude as well :S.|||Like I said: If you start naming tables with plurals, then in the end ALL your tables (or at least a very large percentage) will end up being plural, and so the 's' at the end of every table becomes just another letter you have to type in every line of SQL code and serves no purpose whatsoever. Remember that 99% of all tables will have more than one row and therefore should in your argument be plural in the naming.
And who ever said MS wrote good code, eh?
The date may be 10 years old, but please feel free to provide me with a more recent reference from an SQL standards authority that says 'Yea, we made a mistake in '93, go ahead and use plurals now'
I disagree with you. There's no good reason to use plurals. Using plurals is a nono. Except if you're MS in which case you are expected to write bad code and thus are allowed plurals in table names.
Hi all,
Is there a way to create a dimension property based on fact table used to connect a dimension to another measure group. I found a example where such an attibute is used for a measure expression(http://msdn2.microsoft.com/en-us/library/ms345139.aspx) but I really need a property that I could display to the end user.
thx
Fred
Hi again...just to be a bit more clear on what I'm trying to do...if you look at the example I mentioned....how could the value of the 'ownership' be displayed instead of used in a calculation?
Fred
|||Well, in this example, the user could browse the value of the 'ownership' measure in the intermediate measure group, for a given selection of Store and Organization. If that's not adequate, you could define a fact dimension for the intermediate measure group fact table, and add 'ownership' as an attribute (with the appropriate type).|||Thanks Deepak! The fact dimension will do the trick
Fred
Dear Friends... I'm having a problem...
I want to manually create the identity column for a table...
I have some dataflws, and in each dataflow I insert values in this table...
I need to start the controlflow in a SQL task to get the last ID and save it in a global variable with name D_INST_IDENTITY.
And in each dataflow I have a script component transform, to get the ID... using a local variable COUNTER! and for each row I increment this value...
Until this step there is no problem... the problem starts here...:
I need to refresh the global variable in the final of each dataflow in order that in the next sequence dataflow I have D_INST_IDENTITY refreshed......
D_INST_IDENTITY = D_INST_IDENTITY + COUNTER
How can I do it? I have a RowCount transform next the script component, but generates errors...
What do you think I can do it?
Thanks!!
Can you not just use an Execute SQL Task to get the current max value in the target table and store is in a variable?
-Jamie
|||I can do it... but I have some dataflows, and each dataflow insert in this same table... so, In the finaly of each sequence dataflow, i need tro refresh the identity value... in the begin of each dataflow I need to get this IDENTITY...
Example
1. Dataflow
Return the Initial value of IDENTITY_variable from SQL Task
Refresh Identity_variable
2. Dataflow
Read Identity_variable
Refresh Identity_variable
3. Dataflow
Read Identity_variable
Refresh Identity_variable
|||And In the SQL Task I use:
SELECT MAX(INST_ID)+1 FROM Instrumento
But if teh table is empty returns me an error... Is this statment that is usually used?
|||
PedroCGD wrote:
I can do it... but I have some dataflows, and each dataflow insert in this same table... so, In the finaly of each sequence dataflow, i need tro refresh the identity value... in the begin of each dataflow I need to get this IDENTITY...
You don't do it IN the data-flow, you do it BEFORE the data-flow
Example
1. Dataflow
Return the Initial value of IDENTITY_variable from SQL Task
Refresh Identity_variable
2. Dataflow
Read Identity_variable
Refresh Identity_variable
3. Dataflow
Read Identity_variable
Refresh Identity_variable
So you put an Execute SQL Task before each data-flow. Is there a problem with doing that?
(There are actually some cleverer ways of doing it but for now - let's keep it simple.)
-Jamie
|||
And In the SQL Task I use:
SELECT MAX(INST_ID)+1 FROM Instrumento
But if teh table is empty returns me an error... Is this statment that is usually used?
It really helps if, when you get an error, you tell us the error emssage.
I'm pretty sure I can guess what it is though. Try this:
SELECT ISNULL(MAX(INST_ID), 0) +1 FROM Instrumento
-Jamie
|||Dear Jamie,
In order I have teh best performance, I avoid to use multiple SQL tasks. Imagin that I have 20 dataflows? Why I need to execute a query in database for each dataflow, if I have a counter to automatically give me the IDENTITY column? Do you think is more consistent using SQL Task?
I have found the solution to read a global variavel, and change it inside the script component transform...
I initialize in the Script Component properties the variable INST_IDENTITY as ReadWritevariables, and in order to avoid errors inside the PreExecute method when I read the value, I changed the code as you can see:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim counter As Integer
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
counter = counter + 1
Row.TesteKey = counter
End Sub
Public Overrides Sub PreExecute()
Dim vars As IDTSVariables90 = Nothing
Me.VariableDispenser.LockForRead("User::INST_IDENTITY")
Me.VariableDispenser.GetVariables(vars)
counter = CType(vars(0).Value, Integer)
vars.Unlock()
MyBase.PreExecute()
End Sub
Public Overrides Sub PostExecute()
Me.ReadWriteVariables("INST_IDENTITY").Value = counter
MyBase.PostExecute()
End Sub
Protected Overrides Sub Finalize()
MyBase.Finalize()
End Sub
End Class
I was having errors if I read the value of a ReadWriteVariables inside the PreExcute Method, so, using VariableDispenser I can do it!!
Thanks!!
|||
I have found the solution to read a global variavel, and change it inside the script component transform...
Cool! That is the "other way" that I alluded to earlier.
-Jamie
Hi, I have limited control on the server I use. I can not create data bases but can add tables and edit the data base that has been created for me. So what I would like to do is be able to use a tool such as aspnet_regsql through SQL Server Express to add the tables that Membership would automatically create. Any help in pointing me in the right direction would be great
Thanks
howlinhuskie
Hi,
if you're able to execute sql statements on the server you could use the aspnet_regsql tool to create all the necessary sql statements:http://forums.asp.net/p/994473/1298256.aspx#1298256.
Grz, Kris.
|||
Thanks for looking in on me Kris. Can the aspnet_regsql tool be run using SQL Server Express or do you have to run it through the dos command line? Is there step by step instructions anywhere?
Any help would be great
howlinhuskie
|||Hi,
indeed you have to run it from the command line.
Grz, Kris.
|||Hi,
also take a look at this article:Create Membership tables in another database than the standard aspnetdb.mdf.
Grz, Kris.
|||Thanks again Kris, I do not have the permissions on the server to access the command line.
Mike
|||
howlinhuskie:
I do not have the permissions on the server to access the command line.
And from your own developer pc? If you already have a database and know its name you can use the command line tool from your dev pc, generate the sql statements and later on use Query Analyzer or SQL Server Management Studio to run those queries on the remote database server. You do have permissions to run sql statements on your SQL Server right? In case not you could still generate it and have a DBA install it.
Grz, Kris.
quote:
> I'm trying to adapt the code in the article "How to Create ODBC DSN on
> multiple SQL server Machines"
> (http://www.databasejournal.com/feat...cle.php/2238221) to
> create a script that will automate the creation of SQL Server DSNs.
> The article's code writes the DSN to the appropriate location in the
> registry--HKLM\SOFTWARE\ODBC\ODBC.INI\<DSN Name>. However, the code
quote:
> seem to address how to write the SQL UserID and Password. It appears that
> the SQL driver doesn't store that information in the registry.
> Any suggestions on how to proceed? Thanks!
Has anyone ever come across a reason why someone would manually create a
user table incl. permission flags and not use the inbuilt user/roles
provided by that database? The only reason that stands out for me is to
make the database that bit more portable?
Thanks,
Craig.Craig, it would depend on exactly how the table in question is
constructed and used but some applications provide application based
security. The application might connect to the database using one ID
that is the database owner or has both datareader and datawriter but
via the application limit what end-users can do.
HTH -- Mark D Powell --|||Craig H. (spam@.thehurley.com) writes:
> Has anyone ever come across a reason why someone would manually create a
> user table incl. permission flags and not use the inbuilt user/roles
> provided by that database? The only reason that stands out for me is to
> make the database that bit more portable?
The database-level can be a bit heavy-handed. In our application, our
security scheme on SQL Server is dead simple. All users are added to
a group, and that group is granted execute access on all stored procedures
and select access on most tables.
Then our database includes tables to control access to application
functions, and also access to which accounts and customers a user may
see.
True, our system started its life in the days of 4.x when the permission
system in SQL Server was far less sophistcated than today. But since
our securable entities are not SQL Server entities, I can't see how
SQL Server could help us, even if we were to make a complete restart.
--
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
When you create a Solution/Project in Management Studio, it creates 3 folders for you - Connections, Queries, Miscellaneous.
It does not appear that there exists the ability to create your own set of folders, either under the project or any of the 3 provided folders. Does anyone know of a way to do this?
If you are working on a project that has hundreds, perhaps thousands of stored procedures, views, etc., there currently seems to be no way to organize them. If this is true, this is an incredible MS oversight!
Thanks!
You have found the growing pain problems with the tool...maybe future versions will correct it.|||I would hope so! Since Mgmt Studio is based upon the Visual Studio shell, it doesn't seem like it would be that hard. In fact, I would think they would have had to specifically disable that functionality in this version, it seems so basic and fundamental to an IDE.|||This is one of our most requested features. You can track the progress of this at http://connect.microsoft.com.
Here is the direct link to the issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124787
Vote on it and let us know that it is important to you.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/
I already did. I also started my own post to this issue a long time ago.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127202
|||Ahh, nice... even though your solution is not solved, can you mark the post as answered?
When you create a Solution/Project in Management Studio, it creates 3 folders for you - Connections, Queries, Miscellaneous.
It does not appear that there exists the ability to create your own set of folders, either under the project or any of the 3 provided folders. Does anyone know of a way to do this?
If you are working on a project that has hundreds, perhaps thousands of stored procedures, views, etc., there currently seems to be no way to organize them. If this is true, this is an incredible MS oversight!
Thanks!
You have found the growing pain problems with the tool...maybe future versions will correct it.|||I would hope so! Since Mgmt Studio is based upon the Visual Studio shell, it doesn't seem like it would be that hard. In fact, I would think they would have had to specifically disable that functionality in this version, it seems so basic and fundamental to an IDE.|||This is one of our most requested features. You can track the progress of this at http://connect.microsoft.com.
Here is the direct link to the issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124787
Vote on it and let us know that it is important to you.
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/
I already did. I also started my own post to this issue a long time ago.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127202
|||Ahh, nice... even though your solution is not solved, can you mark the post as answered?
|||I agree it is a huge MS oversight... fortunately theres a 3rd party solution available... Here's the link SQL Server Management Studio 2005 Project Plugin|||I agree it is a huge MS oversight... fortunately theres a 3rd party solution available... Here's the link SQL Server Management Studio 2005 Project Plugin
