|
I want to change the old store to new store by using the query in MSSQL. For example,
Staff_Id Name Transaction_Date New_Store Old_Store
--------------------------------------------------
1 AA 1.1.2017 1
1 AA 1.2.2017 2
1 AA 1.3.2017 3
1 AA 1.4.2017 1
1 AA 1.5.2017 4
I want to change the table to following like that
Staff_Id Name Transaction_Date New_Store Old_Store
--------------------------------------------------
1 AA 1.1.2017 1 NULL
1 AA 1.2.2017 2 1
1 AA 1.3.2017 3 2
1 AA 1.4.2017 1 3
I wrote the query like that, but it's not true, it's output randomly
Update EC1 SET EC1.Old_Store=EC.New_Store
From Employee_Change EC , Employee_Change EC1
WHERE EC.Staff_Id=EC1.Staff_Id
AND EC.Transaction_Date>=EC1.Transaction_Date
|
|
|
|
|
Something like this should work:
UPDATE
EC
SET
Old_Store =
(
SELECT TOP 1 New_Store
FROM Employee_Change As EC1
WHERE EC1.Staff_Id = EC.Staff_Id
And EC1.Transaction_Date < EC.Transaction_Date
ORDER BY EC1.Transaction_Date DESC
)
FROM
Employee_Change As EC
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This has been answered at Change old store to new store[^]
In future, do not cross-post across forums. Pick one and be patient - we do this in our spare time for free - sometimes it can take a little while 
|
|
|
|
|
Hi,
I am running an SSIS Package which takes FileName as Variable and runs the Package, strangely when I run the Package from Sql Agent Job Package I am getting this error: Did not read byte array properly
But when I run the Package, if the file is open, its reading the file and running the Package properly, if I close the File it gives me error, the Package is using the Jet 4.0 drivers and File has multiple sheets in it, I am giving the name of the second sheet to read,
- is it because of the multiple sheets or
- is it because of the Jet drivers,
- The file path is too long (I saw its 150 characters as it shows, the variable datatype that holds the File name is String)
- or if the File full Path combined with connection string value, does it become too long to be hold by the Connection string of the Source Connection?
I am not sure what is the reason for its throwing this error.
Any suggestions welcome please need some help. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Abdul asked: 1.is it because of the multiple sheets
Try replacing "the file" (I'm guessing this is a workbook) with one with only a single sheet and see if the problem still happens
Abdul asked: 3.The file path is too long (I saw its 150 characters as it shows, the variable datatype that holds the File name is String)
4.or if the File full Path combined with connection string value, does it become too long to be hold by the Connection string of the Source Connection?
Try shortening the path - e.g. put it into a temp folder straight off the root, or map a drive to the location.
Given that it "works" when you have the file open though, it is unlikely to be any of those. The suggestions are just to indicate that you could have easily discounted them for yourself by experimentation.
You are using very old Jet Drivers and you have not mentioned which version of Office or SQL. Try installing at least ACE 12.0 and see if that fixes it.
Beyond that, without knowing what the package is trying to do we're fumbling about in the dark
|
|
|
|
|
Yeah we are using only Jet Drivers, but its limitation on Server for now. The office file is .xls file which is 97 version I think.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: if the file is open, its reading the file and running the Package properly
You might want to examine that assumption.
The fact that it doesn't produce an error that you see, is not sufficient to prove that it worked. It must process the file and produce output that it could have only gotten from the file.
|
|
|
|
|
I'm getting ready to start a new C#/WPF project for a client. The database will be SQL Server. Myself and another developer will both work remotly.
So there's two development PC's, each with SQL on them. Then my server will be used to test, so there's a copy of the DB there. Then there's the production DB on the client's server.
I expect that I'll be creating, editing, and deleting DB objects continuously. I will probably use scripts for these changes so I can then run the scripts on either the test or production servers. Also, other developers will need to run them.
The question is about how to manage the changes/scripts. What process do you follow for this? Is there a naming convention? How do you keep everything in sync?
Thanks
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
In a previous position, we used the following DB script pattern:
1) For each major release, have a set of "Create" scripts.
a) Create Table_1.0.0, Create_Index_1.0.0, Create_Sysdata_1.0.0 (or something like that)
2) Each time there is an upgrade, create a set of scripts
a) Upgrade_Table_1.0.0_1.0.01, Upgrade_index_1.0.0_1.0.1 (and so on)
Make sure you are using a source code control system where you would be checking in not only your source code, but your SQL scripts.
When you have a "build" you will also pickup the correct version of database scripts.
Also, you should have a Build / Deploy box where you can Create or Upgrade databases anytime for QA testing. It is always a good test to be able to take a backup of your client's database and run the upgrade scripts to prove them out.
The only version control system I've used is Subversion. If you are not familiar with them, check them out and setup a proper team development environment, you will save yourself lots of headaches.
Just my 2 cents.
David
|
|
|
|
|
General concept
- Database itself has a version
- Wrap the changes into an application that applies those to the database
- Use a table in the database that tracks the current version.
An existing tool for this is liquibase. Seemed decent when I used it.
You can roll your own as well.
And additional feature that I consider essential but it adds complexity is that the application also tracks the version of the database it expects. If the database is the wrong version (table above) then the application will exit on start up.
|
|
|
|
|
We use Red-Gate SQL tools against the UAT server. We also make a strong use of schemas, common objects use dbo, specific object use a different schema, a developer generally works on one schema at a time and dbo changes are discussed before implementing.
Scripts are run against the UAT server using SQL Compare. Master table data is synched via SQL Data Compare.
PS I do not work for Red-Gate but have been using their paid for tool set well over a decade.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This is how I've done it for years....right after login, the application compares db version to app version, applies changes (logic in the app itself) if needed. If the version changed, the db version is updated to reflect that. In addition, I also use a special database table that records schema changes as they are made.
If it happens that the database version is higher than the app, the app prompts for an update. If the user declines, the app complains and exits.
btw, comparing version numbers is much easier if you convert them to long ints using something like this:
intAppVersion = (major * 100000) + (minor * 1000) + revision
This also makes it easier to run a loop through db version checks until you hit the current app version.
"Go forth into the source" - Neal Morse
|
|
|
|
|
That sounds very similar to Entity Framework Code First Migrations[^].
However, I don't think that's a good approach. Apart from your application needing to check the database version every time it connects, it also has to connect as a user which has permission to modify the structure of the database. That always seems like a violation of the principle of least privilege[^] to me. 99% of the time, your application doesn't need to modify the database structure, so it shouldn't have permission to do so.
You might be able to work around that by using a second login with the elevated permissions. But in most cases, I think it's easier to move the database upgrade code outside of the application, and manage it as part of the upgrade process.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: That sounds very similar to Entity Framework Code First Migrations
Maybe they got it from me?  I've been using this approach since about Y2K.
You do have some good points regarding principle of least privilege, and I wholeheartedly agree with that philosophy/architecture for some applications...most of the ones I deal with demand simplicity and the ability for an end user to install a module without help from IT, hence dbo.
"Go forth into the source" - Neal Morse
|
|
|
|
|
I have the following tables and values:
t_cars
nCars_ID sName sModel sIdentifier
1 BMW 3 series D-78-JHG
2 Volvo C30 B-56-KHT
3 Fiat Doblo H-72-ABN
4 Volvo C40 J-78-YTR
t_feature
nFeature_ID sName
1 CMC
2 Doors
3 Color
4 Type
5 Weight
6 Engine
7 Power
t_cars_feature
nCarsFeature_ID nCars_ID nFeature_ID sValue
1 2 1 2500
2 2 2 5
3 2 4 Diesel
4 2 3 Green
5 3 1 1900
6 3 2 3
7 3 4 Otto
8 3 5 2300 KG
9 1 1 1900
10 1 3 Blue
11 1 4 Diesel
12 1 5 2100 KG
I need to retrieve from DB the cars that has CMC feature, has Color feature, AND CMC = 1900 AND Color = 'Blue' ONLY
I have tried:
SELECT t_cars.sName, t_cars.sModel, t_cars.sIdentifier
FROM t_cars, t_feature, t_cars_feature
WHERE t_feature.nFeature_ID = t_cars_feature.nFeature_ID
AND t_cars.nCars_ID = t_cars_feature.nCars_ID
AND [/*condition that get me cars that has CMC feature, has Color feature, AND CMC = 1900 AND Color = 'Blue' ONLY*/]
I have tried the condition like that:
Trial 1:
AND t_feature.sName = 'CMC'
AND t_feature.sName = 'Color'
AND t_cars_feature.sValue = '1900'
AND t_cars_feature.sValue = 'Blue'
and get me nothing
I have also tried:
Trial 2:
AND t_feature.sName IN ('CMC','Color')
AND t_cars_feature.sValue IN ('1900','Blue')
and get me all records that has CMC 1900 OR color 'Blue' (probably I got here cartesian product)
In real situation I could have several t_feature.sName values, and several t_cars_feature.sValue values, that is why trial 1 are not suitable for me ...
Can you help me ? Thank you.
|
|
|
|
|
You need to select on the cars_features table more than once, so:
SELECT C.sName, C.sModel, C.sIdentifier
FROM t_cars AS C INNER JOIN t_cars_feature AS CF1 ON CF1.nCars_ID = C.nCars_ID
INNER JOIN t_cars_feature AS CF2 ON CF2.nCars_ID = C.nCars_ID
WHERE (CF1.nFeature_ID = 1 AND CF1.sValue = 1900)
AND (CF2.nFeature_ID = 3 AND CF2.sValue = 'Blue')
sName | sModel | sIdentifier
----------------------------
BMW | 3 series | D-78-JHG
|
|
|
|
|
First crate a query that selects the link records (t_cars_feature) you expect to see.
Select * from t_cars_feature where (FeatureID = 1 and value = 'CMC') and (FeatureID = 2 and value in (3,5)) ...
Then change the query to only get the CarID
Select CarID from t_cars_feature where (FeatureID = 1 and value = 'CMC') and (FeatureID = 2 and value in (3,5)) ...
Once you are happy with the results wrap the first query to select the cars
Select * from Cars where CarID in (insert query 1)
What you are missing is the heavy use of brackets. It is still an ugly database design.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
It shows both the Jet and Ace drivers on a Server machine, and user the Sql Agent Job is running with, doesn't have admin privileges. The Packages that are running with Jet drivers are working fine on that Server where as the Packages that are using Ace drivers are not running Properly. There are various questions and eye brows that are raising here
- There are Ace 14.0 drivers there on that machine, were the Ace 12.0 drivers overwritten by Ace 14.0 when we installed newer version of Ace or office pack
- Don't the Ace 14.0 drivers which are advanced than the Ace 12.0 have backward compatibility, why is it? When we try Ace 12.0 drivers with the Packages that are using csv or flat files are working fine where as only the Excel files both (xls and xlsx) are not working properly why?
a. Are the Ace 14.0 drivers backward compatible with the csv and flat files or do the Ace 12.0 drivers can import from the csv and flat files without Admin
privileges Many questions are raising with this odd behavior. Because the csv and flat files are getting imported without admin privileges too
b. Are the Ace 12.0 drivers not available only 14.0 are running all this but because of the registry entry or some uncleaned stuff from the installation is
showing the Ace 12.0 drivers in the drivers list? Many many questions are raising. - As the Packages with the Jet drivers are working fine I am not understanding what is missing or messing up here, is it the unavailability of Ace drivers or do the Ace drivers need Admin privileges and Jet drivers don't? If unavailability is the case then why Ace 12.0 drivers are fine with csv and flat files?
The problem for us in continuing with the Jet drivers is, we have some packages with xls which is fine with Jet drivers but we have some Packages with the xlsx files which are not going to work with the Jet drivers.
Is there any buddy who can answer me these questions? Any help would be greatly appreciated my friends. I am also researching doing lot of studies about behavior of these drivers.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Are you talking about SSIS packages?
I recommend using only ACE, but you may need to run your packages as 32-bit.
|
|
|
|
|
I did, but still Ace drivers are not working but Jet drivers are working on my machine, but Ace drivers are showing in the drivers list, with Admin rights Ace drivers are also Working, when user is not Admin Jet drivers are working fine, how is it different for different drivers when the user is same?
I have Ace 14.0 drivers but Packages by default taking only Ace 12.0 drivers, when I check in the drivers list Ace 12.0 drivers are showing in the list are the Ace 12.0 drivers cheating that they show-up from registry entries but don't exist?
- Why SSIS Packages only take Ace 12.0 drivers, why isn't there backward compatibility?
- why don't they take Ace 14.0 drivers?
- Why Jet drivers can read the Excel files without the user that doesn't have Admin privileges but Ace 12.0 drivers can't?
Any help would be greatly helpful friends - thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: with Admin rights Ace drivers are also Working,
So rephrasing to make it very clear what the problem is.
1. When you run the application ON the target machine with admin privileges it both drivers work.
2. When you run the application ON the target machine with non-admin privileges, doing exactly the same as above for 1, then only one driver works. The other fails.
If so your problem is with the non-admin user and/or the access rights for the driver that does not work.
|
|
|
|
|
Hi all,
I am passing column names in dynamic sql like 'col1, col2, col3' + ', ''' + @reportId + '''', up to col3 all the columns are giving correct values, but the @reportId is a string which has value as '1-085' that's being converted into integers and giving me the value '-084'. I mean may its calculating the expression and returning the resultant value.
But I want that string to be as it is, means I want that value as '1-085' instead of a calculated value ie -84, is there anyway to do it. Any help would be greatly helpful, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
If it is a string then it will be saved as a string. Your code must be doing something strange.
|
|
|
|
|
No I am passing it as a string only from an ssis package variable which is of type string, I am appending both these strings as 'col1, col2, col3' + ', ''' + @reportId + '''' and passing it as nvarchar parameter into a stored procedure
Exec [ETL].[PopulateStageTable] ?, ?, ?, ?, ?, ?, ?
There are other Parameters but they are not giving any problem excel this, I checked even the execute sql too has this parameter defined as nvarchar and same thing in the Stored procedure too, nothing is converted to number or anything like that
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Rephrasing what the previous response was suggesting (I believe.)
You posted a line where @reportId is used. Nothing in that line would change the value of that.
That line has nothing to do with the value in @reportId. At that point it already has the value you are seeing.
Consequently you are doing something before that which causes it to have that value. You need to find that and correct it.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
