|
I have to construct a view to feed a 3rd party application.
On the application database they have a boolean field CanAccess which is a blanket item for access to any location.
On the master database where I'm building my view, the contact has a LocationAccess table containing access rights for each explicite location.
What I need in my view is a way of returning something like Count(select * from LocationAccess) > 0 for each contact I'm running through. Here is what I've got so far. If anyone can suggest how I add this last item I'd greatly appreciate it!
(BTW - yes the orderby is required and thus the TOP 100% is also required)
SELECT TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone,
dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance,
dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber,
dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox,
dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode,
dbo.ContactMaster.BmcCustKey, CASE WHEN dbo.ContactMaster.Role = 1 THEN 1 ELSE 0 END AS IsAdministrator,
CASE WHEN dbo.ContactMaster.Role = 6 THEN 1 ELSE 0 END AS IsDecisionMaker,
CASE WHEN dbo.ContactMaster.Role = 5 THEN 1 ELSE 0 END AS IsAccountingAP
FROM dbo.ContactMaster INNER JOIN
dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity LEFT OUTER JOIN
dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity LEFT OUTER JOIN
dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey
|
|
|
|
|
Options to tackle this
sub query (select count(*) from loaction....) - not recommended as it can destroy a server.
Use a join to the location table and a group by your existing fields - just plain ugly
Create another view that counts the location info and then use a join just like any other table/view - probably the way I would go
create a UDF that does the same as the view - can you use a UDF in a view?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK -- so let's say I create a view that gives me ContactIdentifier and Count.
So when my view is running how do I specify that other view as input to the query.
Do I do a join between the contact table and that view?
|
|
|
|
|
Assuming that every contact will have 1 or more counted record then use an inner join from your existing view to the count view and this will filter form the filter. If not then do a left join.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hy,
I want to fetch records from xlsheet...i used the following query in my local server
DECLARE @SQLconnect VARCHAR(8000), @FileName varchar(256)
SET @FileName='C:\CopyofOTFormat.xls'
SET @SQLconnect =
'SELECT * INTO ##tmpOrders FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @FileName + ''', ''SELECT * FROM [Sheet1$] '')'
PRINT @SQLconnect
-- SELECT * INTO ##tmpOrders FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=F:\data\export\excel\Top10.xls', 'SELECT * FROM [Sheet1$]')
EXEC (@SQLconnect)
SELECT * FROM ##tmpOrders
SELECT COUNT(*) FROM ##tmpOrders
-- 2452
DROP TABLE ##tmpOrders
It works in my local server but when i run it in the online server...it says that the xlsheet could not be found.
Is there any way i can access it in online server without actually saing it over there
Thanks & Ragards
raghvendra panda
|
|
|
|
|
raghvendrapanda wrote: Is there any way i can access it in online server without actually saing it over there
The online server needs access to the "F:\" drive that you're referring to in your connectionstring. That means that the Excel-sheet needs to be saved on the machine that's going to read it.
I are Troll 
|
|
|
|
|
Dear All,
I have restored a new version of my database into sql 2005, but in old version there were some procedures, function and tabels which I really need them and they are not in new restored version.
Is there any way i can undo my restore or is there any way to get those tables and procedures from old database before restore.
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Not AFAIK.
Unless you have a more recent backup I think you are screwed.
[tongue firmly in cheek] surely you have all you procedures in a source control system and can retrieve them form there.
Actually we take daily backups of our development boxes and keep them in (spit) sourcesafe. We also keep local copies on our workstations so at any time there will be up to 6 recent copies of a database available. This is useful when some twat (3 of them in the last 6 months) uses SQL Compare and REMOVE all the new procs from the development box.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I also do not know how you can unrestore a database. The only thing I can think of is that the LDF files are immense, and might help an expert undo some changes.
|
|
|
|
|
Given the following Query, I get the desired results, however, the result set is sorted on the PLine Column.
I would like to Order By the ID Column, or Better yet, the Factory.Factory_No (varChar), ProdnLine.ProdnLine_No (int)columns.
DECLARE @LineKgs Table(
ID Int, PLine VarChar(7),kgs Decimal(18,7), TDate nVarChar(10))
INSERT INTO @LineKgs
SELECT f.Factory_No + '/' + CAST(pl.ProdLine_No As VarChar), pl.ProdLine_ID,
mt.Qty, CAST(month(mt.TransDate) AS VArChar(10))
FROM Production pr
LEFT OUTER JOIN ProdLine pl
ON pl.ProdLine_ID = pr.ProdLine_ID
JOIN MaterialTransaction mt
ON pr.Prodn_ID = mt.Prodn_ID
JOIN Factory f
ON pl.Factory_ID = f.Factory_ID
WHERE mt.TransDate BETWEEN '1/1/2010' AND '12/31/2010'
SELECT ID, PLine, [1] As Jan, [2] As Feb, [3] As Mar, [4] As Apr,
[5] As May, [6] As Jun, [7] As Jul, [8] As Aug,
[9] As Sep, [10] As Oct, [11] As Nov, [12] as Dec
FROM( SELECT ID, PLine, kgs, TDate
FROM @LineKgs) p2
PIVOT(SUM(kgs)
FOR TDate In ([1], [2],[3],[4],[5],[6],
[7],[8],[9],[10],[11],[12])) As P
Current Result
ID PLine Jan Feb Mar ...
=============================================
1 Fac1/1 8497 60012 19503 ...
2 Fac1/2 104772 152379 33795
3 Fac1/3 76338 113653 40742
4 Fac1/4 197339 333348 118314
5 Fac1/5 200986 363314 106225
6 Fac1/6 35048 189619 44345
7 Fac1/7 45632 133981 52921
8 Fac1/8 58387 185468 39916
9 Fac1/9 34625 76026 69479
10 Fac2/1 420 4150 828
19 Fac2/10 10816 25213 2681
20 Fac2/11 13680 30765 4597
21 Fac2/12 10576 NULL 19068
11 Fac2/2 251 333 NULL
12 Fac2/3 433 1536 NULL
...
Desired Result:
ID PLine Jan Feb Mar ...
=============================================
1 Fac1/1 8497 60012 19503 ...
2 Fac1/2 104772 152379 33795
3 Fac1/3 76338 113653 40742
4 Fac1/4 197339 333348 118314
5 Fac1/5 200986 363314 106225
6 Fac1/6 35048 189619 44345
7 Fac1/7 45632 133981 52921
8 Fac1/8 58387 185468 39916
9 Fac1/9 34625 76026 69479
10 Fac2/1 420 4150 828
11 Fac2/2 251 333 NULL
12 Fac2/3 433 1536 NULL
...
20 Fac2/11 13680 30765 4597
21 Fac2/12 10576 NULL 19068
...
As always, your guidance is most appreciated.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
I must be missing something, add an order by to your select after as P
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you Mycroft, I admit to not reading the docs rigorously 
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Hi Team,
Functional requirement is that to Ignore Duplicate Records while (based on a unique column )while doing Data Flow in SSIS.
Please help me out from this scenario.
Regards
Tarun Singh
Sr. Software Engineer.
mailto:tksingh@zenta.com
|
|
|
|
|
Have you looked through here?
Working With Data in Data Flows[^]
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Hi,
I need to move several databases to another computer, is there a utility for this on SQL 2005 server? I hope I don't have to recreate the database and tables. I'm new to this, so thanks for the help.
Lou
|
|
|
|
|
I would think the easiest would be to detach them, copy them over, and reattach them. Alternatively, you can back them up and restore them. Both methods are pretty simple.
Scott
|
|
|
|
|
First a given, backup the database. Then you could use the backup to "restore" to the new computer. This works well because it should copy the permissions of the database too.
here is some additional reading from microsoft's site, Hope this helps...
http://support.microsoft.com/kb/314546/en-us
There are other ways as well but this may be the easiest for someone new to SQL server.
|
|
|
|
|
At least it worked, though badly.
For months I've been playing with SQL Server Express 2008 on my server, building databases and connecting to them just to see what happens when I try different things. To be honest, I haven't learned much, since my time is so limited, but it's been fun. Last night I created a new DB with the intent of banging out a tool to use at work for tracking some measurements I have to record. I can add functionality later, but I thought I'd at least get the measurements saved for now.
Building the DB in the management console was a breeze, though tedious, but when I switched back to the client computer, created a skeleton app in C#, and tried to add the DB connection, it all fell apart. It's telling me that the database doesn't exist, or I don't have permission to open it. That's all - no other clues. Yet from the same app and computer, if I try to connect to one of the other DBs I have on the same instance of SQL Server 2008, I get no errors at all. That tells me that SQL ServerX is configured correctly, ie, Remote Connections enabled, TCP/IP enabled and using port 1433, else I'd get the same error from all the other DBs installed. That also implies that the permissions are correct, as I built them all using the same user account, in the same way.
What the heck am I missing here? Nothing on Google or MSDN has helped, not even a clue. Was there a checkbox I missed when creating this new DB that I missed, something that says "Publish this database in the Browser Service?" I don't recall seeing anything like that.
The only change that I've noted is that, since I created the other DBs on the server, several times the Windows Update service has run automatically. Did anything from Microsoft recently change the way SQL Server works? If so, there's no documentation I can find...
Any ideas? Should I just delete the DB and start over? I hate it when I'm all fired up with an idea, motivation at top levels, ready to build something useful, then some crap like this lets all the wind out of my sails.  I've wasted two precious nights on this nonsense, and might not have that luxury again for weeks!
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I'd be inclined to think i i your connection, you don't mention testing a connection from your new app to another database.
Next check the credentials you are using have the right permissions, I give my app credentials dbo rights to the database. It's embarrassing the number of times I have to stand back and scratch the head when setting up a new app, it got so bad I set up a shell application with all the needed bits in place and just rename the name space and database when I want to start a new project.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Actually, I tested the connection to all the other databases on the server; they all worked fine. And I do have dbo rights on all of them, including the new one. I'm wondering if something non-obvious went wrong when I created the database on the server. There were no error messages, but 'stuff' happens. Perhaps the best bet is to delete it and recreate it. It's a little one, with just two tables. Frustrating... 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Check the DB security, I don't have a SQL Server on hand right now to check where, but in the management console go to the database, then security and see who has access to it, also at server level check the DB assignments to each security group.
The problem is not related to your app, is in the database security for sure
I want to die like my grandfather- asleep, not like the passengers in his car, screaming!
|
|
|
|
|
I'll do that. It makes sense, in a twisty, Microsoftian way. Since all the DBs were created with the same tool, using the same settings, they should behave identically. But prior to this last one there was an automatic Windows Update. That may have changed some of the default settings in SQL Server. If not, I'm bolluxed; nothing else has changed.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
At least a partial solution...
When creating the database in Management Studio, I used the user name RAWRIGHT\Administrator as the owner. But while reviewing the error logs I found many entries corresponding to my attempts to connect locally reporting a logon failure for RAWRIGHT\rawright. Apparently it considered me connecting from the client PC to be a different user than the local one, which makes sense. What doesn't make sense is that the other DBs on the server don't care what login I use. I still haven't solved that mystery.
But for now, I added the RAWRIGHT\rawright user name to the list of LOGIN objects on the server and I can now connect using the Add Database wizard in Visual Studio. I'm still mystified about why the other DBs work fine, while this one has been a nightmare, but at least I can get on with the project.
If anyone has an explanation I'd appreciate hearing it. MSDN and Microsoft Support have apparently never heard that there's a problem, based on the results from Bing. 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Roger Wright wrote: MSDN and Microsoft Support have apparently never heard that there's a problem, based on the results from Bing.
Did you ever consider the possibility of Bing being a bit subjective when looking for MS problems?
|
|
|
|
|
That thought had crossed my mind. I also considered the possibility that Bing is just as useless as the old MSDN Search, but of course discarded that train of thought as unworthy. 
And I posted too soon. Having spent an hour recreating the database, all the tables, and a diagram with a username that works, I returned to Visual Studio and added the connection successfully. Unfortunately, the wizard connects nicely, but reports that the database contains no tables (nor anything else). Thanks a bunch Microsoft, for a week of fun-filled long evenings... 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
