Exec problem
-
I need to create a table for each user when they log into my db. The table will be named partially using their username. Once the table is created (using Exec to build and run a CREATE TABLE statement with a @tableName parameter for the dynamic table name), a second sproc is called that is supposed to populate the newly created table with a subset of data from a master table based on the users permissions. However, if I try a INSERT INTO statement (obviously I have to ghard code a table name for this) it works fine. But when incorporated into the Exec function with the necessary adjustments, it fails to work at all. It seems to be something to do with the JOINS because if I remove all of the joins (which of course ruins the outcome), it runs. If I add one single join back in, it doesn't work any more.
Here's the code:
This works:
CREATE PROCEDURE RMPM_spFillUserPropertyTable
INSERT INTO ermaster.RMPM_properties_ELVIS_PRESLEY (propertyId) SELECT propertyId FROM RMPM_Properties LEFT JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_cityTowns ON RMPM_addresses.cityTownId = RMPM_cityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId WHERE propertyName IN ( SELECT propertyName FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_ContinentalRegions ON RMPM_Countries.continentalRegionId = RMPM_ContinentalRegions.continentalRegionId INNER JOIN RMPM_Continents ON RMPM_ContinentalRegions.continentId = RMPM_Continents.continentId INNER JOIN RMPM_GlobalRegions ON RMPM_Continents.globalRegionId = RMPM_GlobalRegions.globalRegionId INNER JOIN RMPM_groupItems ON RMPM_GlobalRegions.globalRegionId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = 'global region' UNION ALL SELECT propertyName FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_ContinentalRegions ON RMPM_Countries.continentalRegionId = RMPM_ContinentalRegions.continentalRegionId INNER JOIN RMPM_Continents ON RMPM_ContinentalRegions.continentId = RMPM_Continents.continentId INNER JOIN RMPM_groupItems ON RMPM_Continents.continentId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = 'continent' UNION ALL SELECT propertyName FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_ContinentalRegions ON RMPM_Countries.continentalRegionId = RMPM_ContinentalRegions.continentalRegionId INNER JOIN RMPM_groupItems ON RMPM_ContinentalRegions.continentalRegionId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = 'continental region' UNION ALL SELECT propertyName FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_groupItems ON RMPM_Countries.countryId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = 'country' )
This doesn't:
CREATE PROCEDURE RMPM_spFillUserPropertyTable
@tableName nvarchar(100),
@paramGR nvarchar(30),
@paramCR nvarchar(30),
@paramCON nvarchar(30),
@paramCOU nvarchar(30)
AS
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO dbo.' + @tableName + ' (propertyId) SELECT propertyId FROM RMPM_Properties LEFT JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_cityTowns ON RMPM_addresses.cityTownId = RMPM_cityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId WHERE propertyId IN (SELECT propertyId FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_ContinentalRegions ON RMPM_Countries.continentalRegionId = RMPM_ContinentalRegions.continentalRegionId INNER JOIN RMPM_Continents ON RMPM_ContinentalRegions.continentId = RMPM_Continents.continentId INNER JOIN RMPM_GlobalRegions ON RMPM_Continents.globalRegionId = RMPM_GlobalRegions.globalRegionId INNER JOIN RMPM_groupItems ON RMPM_GlobalRegions.globalRegionId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = ' + @paramGR + ' UNION ALL SELECT propertyId FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_ContinentalRegions ON RMPM_Countries.continentalRegionId = RMPM_ContinentalRegions.continentalRegionId INNER JOIN RMPM_Continents ON RMPM_ContinentalRegions.continentId = RMPM_Continents.continentId INNER JOIN RMPM_groupItems ON RMPM_Continents.continentId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = ' + @paramCON + ' UNION ALL SELECT propertyId FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_ContinentalRegions ON RMPM_Countries.continentalRegionId = RMPM_ContinentalRegions.continentalRegionId INNER JOIN RMPM_groupItems ON RMPM_ContinentalRegions.continentalRegionId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = ' + @paramCR + ' UNION ALL SELECT propertyId FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_groupItems ON RMPM_Countries.countryId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = ' + @paramCOU + ')'
EXEC(@sql)
except if I remove all the joins and related tests against the joined tables.
Help!
-
Why do you have a separate table for each employee exactly??
-
What is the error that is generated?
(Also, why are you doing this?)
I wasn't born stupid - I had to study.
-
You are not filling in the needed single quotes around the parameters, like:
...RMPM_groupItems.itemType = ''' + @paramCON + ''' UNION ALL SELECT ...
Mark -
3 replies/minute. Now we're getting back to our normal speed
. -
Must be a slow day for DBA's. All backups have run successfully and no server errors.
Mark -
Hello,
well the table that each user will get will be the main data that everything else in the app centres around and will need to be accessed from virtually every other query, report in the app. So rather than run through a complex query like this every time to determine which subset of data they are entitled to see, I figured it'd be best to pump it out to their own table every time they log in and then all the other queries can refer to their particular table.
I don't see any errors, it just fails to work. I'm calling the initial sproc from an asp page but I'm not doing on error resume next but everything proceeds ok and in query analyser the straight sql works fine (as it does in the sproc).
I thought I was missing some quotes around the params - thanks - but even if i remove all that stuff and even just include on join (for the sake of it as a test) the function doesn't seem to work.
for instance:
this doesn't work:
CREATE PROCEDURE RMPM_spFillUserPropertyTable
@tableName nvarchar(100),
@paramGR nvarchar(30),
@paramCR nvarchar(30),
@paramCON nvarchar(30),
@paramCOU nvarchar(30)
AS
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO dbo.' + @tableName + ' (propertyId) SELECT propertyId FROM RMPM_Properties LEFT JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId WHERE propertyId IN (SELECT propertyId FROM RMPM_properties)'
EXEC(@sql)
but this does:
CREATE PROCEDURE RMPM_spFillUserPropertyTable
@tableName nvarchar(100),
@paramGR nvarchar(30),
@paramCR nvarchar(30),
@paramCON nvarchar(30),
@paramCOU nvarchar(30)
AS
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO dbo.' + @tableName + ' (propertyId) SELECT propertyId FROM RMPM_Properties WHERE propertyId IN (SELECT propertyId FROM RMPM_properties)'
EXEC(@sql)
-
Just for the record. You can add the userid column to the property table, and then just fetch their info. You can include that logic in a view and it would work very well with very little work.
-
That sounds interesting. Sorry, I'm pretty much a beginner with SQL so some things I ask might seem a bit dumb (just so there aren't too many exasperated groans later).
But if I have a userId column in my properties table how could I store more than one userId against any one property? You see multiple users may have access to the same properties from the property table - or overlapping subsets.
-
Rather than EXEC(@sql), could you PRINT @sql and show us the results?
Thanks
I wasn't born stupid - I had to study.
-
Tables > Users, Properties, UserProperties
Users :
UserID
Name...
Properties
PropertyID
Name
...
UserProperties
UserID
PropertyID
Values
Then you can join those Properties and UserProperties on a specific userid and fetch all the info.
-
Sorry for the delay. I've learned something new already. Once I put the PRINT in place of the EXEC it took a while for me to work out how to get the results. I used QA to call the sproc and pass the params - I take it that's what I was suipposed to do?
Anyway,
this is what I typed in QA:
EXEC RMPM_spFillUserPropertyTable RMPM_Properties_ELVIS_PRESLEY, 'global region', 'continental region', 'continent', 'country'
and here's the result:
INSERT INTO dbo.RMPM_Properties_ELVIS_PRESLEY (propertyId) SELECT propertyId FROM RMPM_Properties LEFT JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_cityTowns ON RMPM_addresses.cityTownId = RMPM_cityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId WHERE propertyId IN (SELECT propertyId FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_ContinentalRegions ON RMPM_Countries.continentalRegionId = RMPM_ContinentalRegions.continentalRegionId INNER JOIN RMPM_Continents ON RMPM_ContinentalRegions.continentId = RMPM_Continents.continentId INNER JOIN RMPM_GlobalRegions ON RMPM_Continents.globalRegionId = RMPM_GlobalRegions.globalRegionId INNER JOIN RMPM_groupItems ON RMPM_GlobalRegions.globalRegionId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = 'global region' UNION ALL SELECT propertyId FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_ContinentalRegions ON RMPM_Countries.continentalRegionId = RMPM_ContinentalRegions.continentalRegionId INNER JOIN RMPM_Continents ON RMPM_ContinentalRegions.continentId = RMPM_Continents.continentId INNER JOIN RMPM_groupItems ON RMPM_Continents.continentId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = 'continent' UNION ALL SELECT propertyId FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_ContinentalRegions ON RMPM_Countries.continentalRegionId = RMPM_ContinentalRegions.continentalRegionId INNER JOIN RMPM_groupItems ON RMPM_ContinentalRegions.continentalRegionId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = 'continental region' UNION ALL SELECT propertyId FROM RMPM_properties INNER JOIN RMPM_addresses ON RMPM_Properties.addressId = RMPM_addresses.addressId INNER JOIN RMPM_CityTowns ON RMPM_addresses.cityTownId = RMPM_CityTowns.cityTownId INNER JOIN RMPM_Countries ON RMPM_CityTowns.countryId = RMPM_Countries.countryId INNER JOIN RMPM_groupItems ON RMPM_Countries.countryId = RMPM_groupItems.itemId WHERE RMPM_groupItems.itemType = 'country' )
-
Huh! That's really clean and straight forward. I don't quite know how I managed to not even think about that option since I spent about 4 weeks with the inital design. I may well rethink what I'm doing based on that.
Thanks for that.
-
Someone smart here said this once : "When you see yourself in the face of an impossibly complicated task, look at a way to simplify the task or avoid it altogether."
Can't remember the name though
. -
You know, I got home and I picked up a book that I'd bought a while ago on SQL server and found the answer pretty quickly. Because I do all my work against tables using SPROCS rather than directly accessing the tables, none of my users have any permissions to the tables themselves. What I didn't realise is that unlike plain SQL in a SPROC which runs against anything so long as the user has permission to run the SPROC in the first place, the Exec function carries out it's work as the user and their permissions to the target object matters. Since my users don't have select permissions on the tables I guess that's why nothing is happening.
Thanks for all your help - I've learned a thing or two from you guys.
Cheers.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply