User profile for Chris Auckland

Thanks for your post. I wanted to contact you via your email address to try and gather some more information, but unfortunately I couldn't reach you. When I try to parse the SQL in SSMS I get a syntax error, presumably because the code extract is not valid on its own. I have never created a cursor using a WITH statement before and I have not found any documentation that supports this kind of declaration. As a result of this I have has little success in recreating a test case to confirm if this is a bug or not. If the SQL Code parses in SSMS but SQL Prompt identifies an error, then it sounds like it could be a bug with SQL Prompt. However, I will need to be able to recreate the issue in order to report the bug to the developers. It would be really useful if you could supply me with code that demonstrates the problem. I look forward to hearing from you. / comments

Thanks for your post. I wanted to contact you via your email address to try and gather some more information, but unfortunately I couldn't reach you. When I try to parse the SQL in SSMS I get a syn...

0 votes

Thanks for your post. You might find this easier if you use aliases in the query. You can then define the 'otherDatabase' as something like otherDatabase.dbo.TableInOtherDatabase as _tiod, then whenever you refer to it you can use _tiod.pColumn. It is also possible to enable SQL Prompt to automatically assign an alias to a table, see: SQL Prompt > Options > Auto Insert > Aliases. If you have assigned aliases to the tables, then your query might look something like: select ticd.aColumn , ticd.bColumn , _tiod.cColumn , ticd.dColumn from dbo.TableInCurrentDatabase as ticd inner join otherDatabase.dbo.TableInOtherDatabase as _tiod on ticd.yColumn = _tiod.zColumn I hope this is helpful. / comments

Thanks for your post. You might find this easier if you use aliases in the query. You can then define the 'otherDatabase' as something like otherDatabase.dbo.TableInOtherDatabase as _tiod, then whe...

0 votes

Thanks for your post. If you uncheck the SQL Prompt option (SQL Prompt > Options > Inserted Candidates > Formatting) 'Qualify Column Names', then when you select the column name from the candidate list, it will not prefix the column with the table name or alias. I hope this is helpful. / comments

Thanks for your post. If you uncheck the SQL Prompt option (SQL Prompt > Options > Inserted Candidates > Formatting) 'Qualify Column Names', then when you select the column name from the candidate ...

0 votes

Thanks for your post. Highlighting of the matching parenthesis is not actually a feature of SQL Prompt 3; it is a feature of the SSMS 2008 intellisence. This feature will only work if you are querying SQL 2008 databases with SSMS 2008. There was talk of implementing our own facility to match parentheses, but since Microsoft have already incorporated this into SSMS 2008, it is unlikely that we will develop our own method. Let me know if you need any more information. / comments

Thanks for your post. Highlighting of the matching parenthesis is not actually a feature of SQL Prompt 3; it is a feature of the SSMS 2008 intellisence. This feature will only work if you are query...

0 votes

Thanks for your post. If you highlight the object before you invoke the candidate list, then once you select the new object, the entire word will be replaced. If you do not highlight the word, then the new object will only replace the characters before the cursors current position. I hope this is helpful. / comments

Thanks for your post. If you highlight the object before you invoke the candidate list, then once you select the new object, the entire word will be replaced. If you do not highlight the word, then...

0 votes

Thanks for your post. Normally we see this kind of error due to some form of schema corruption. Often it turns out to be down to an object not having an owner, hence the NULL returned. However, this is not always the case. The first thing to check is that the account you are using has permissions to retrieve all the meta data for the schema. Using a sysadmin account should prove if this is the problem or not. You could then rerun the project and see where it fails. It should say the point at which the registration fails, e.g. "Reading Views". This might help you to track down the problem object. If this doesn't help, you can run a SQL Server Profiler Trace and catch the exact query that causes SQL Compare to fail (usually the last query in the trace for 'SQL Tools'). If you run this query manually through SQL Server, the results returned should contain enough information to find out which object has caused the problem. If you have not run a trace before, here is some information on how to do it: http://www.red-gate.com/supportcenter/G ... 000082.htm Feel free to send me the query and the results if you need me to investigate this further. A screenshot of the SQL Compare error would also be useful. / comments

Thanks for your post. Normally we see this kind of error due to some form of schema corruption. Often it turns out to be down to an object not having an owner, hence the NULL returned. However, thi...

0 votes

Thanks for your post, and sorry for the delay. Currently there is no method to set the project to ensure unique columns names when exporting a CSV file. It is also not possible remove the unnecessary columns from the 'Additional' and 'Missing' CSV files. I have created feature requests for both of these issues. Hopefully they will be addressed in a future version. For your reference the feature tracking codes are SDC-839 and SDC-838 respectively. I have associated this ticket with the requests, so I will be able to update you on any developments. Let me know if you need any further information. / comments

Thanks for your post, and sorry for the delay. Currently there is no method to set the project to ensure unique columns names when exporting a CSV file. It is also not possible remove the unnecessa...

0 votes

Thanks for your post. Usually invalid SQL will break the SQL Prompt parser and cause the candidate list to function incorrectly. It seems that even though the syntax for the 1st IF EXISTS is not valid, the candidate list is still suggesting the right candidates. The SQL is invalid because there is no statement after the IF condition. This is not happening within the BEGIN END block, but if you add a statement after the IF condition, then the candidate list will work correctly. e.g. BEGIN IF EXISTS (SELECT * FROM Table1 WHERE <<cursor here>>) Print 'Record exits - Update' END It is expected behaviour that invalid SQL will cause problems with the candidate list. The development team are currently looking into ways that we can handle invalid SQL and still keep consistency with the candidate list. I hope this is helpful. Let me know if you need any more information. / comments

Thanks for your post. Usually invalid SQL will break the SQL Prompt parser and cause the candidate list to function incorrectly. It seems that even though the syntax for the 1st IF EXISTS is not va...

0 votes

Thanks for your post. Using the steps you outlined I was able to recreate the fault using SQL Compare 6.2. It seems that SQL Compare will notice the textual difference in the SQL, but will not mark it as a semantic difference. However, I believe this has been identified and resolved by our development team, as the latest version SQL Compare 7.0 recognises the difference in the columns. You will be able to fix this problem by downloading and installing SQL Compare 7.0. You can download the new version from our website, or by using the 'check for updates' function within the tool. I hope this is helpful / comments

Thanks for your post. Using the steps you outlined I was able to recreate the fault using SQL Compare 6.2. It seems that SQL Compare will notice the textual difference in the SQL, but will not mark...

0 votes

Thanks for your post. I'll attempt to answer each of your questions in turn: 1. As long as you can connect to the remote SQL Server, you will only need to have SQL Compare installed on your workstation. If you can connect to the SQL Server using SSMS or EM then you shouldn't have any problems connecting with SQL Compare. If you would like to automate the creation of snapshots, or synchronisation of the schemas you can use the SQL Compare command line interface. As long as you have permission to connect to the SQL Server you will be able to create a batch file to automate the process on your workstation. See: http://www.red-gate.com/supportcenter/C ... %5c456.htm 2. The licensing for SQL Compare works on a per-user basis. This means that as long as you are the only person using the license, you can install it wherever you need to. However, in your situation I think you would just need to install it on your workstation and remotely connect to the SQL Servers. 3. This is indeed a permissions issue. In some circumstances a user might not have the permissions to view object definitions, this will cause the symptoms you reported. You should be able to fix it by granting VIEW DEFINITION for the user on the database. Hopefully this should resolve the problem. Please see the following article regarding minimum permissions required for using SQL Compare: http://www.red-gate.com/supportcenter/C ... 000072.htm I hope this information is helpful. Let me know if you need anything further. / comments

Thanks for your post. I'll attempt to answer each of your questions in turn: 1. As long as you can connect to the remote SQL Server, you will only need to have SQL Compare installed on your worksta...

0 votes

How can we help you today?

Chris Auckland

Comments