1,079 reputation
31739
bio website
location Sydney, Australia
age
visits member for 2 years, 2 months
seen Oct 2 at 1:22

Jan
20
comment sql agent job hangs without any errors or warnings
Sorry, I was typing the comments above from my mobile and made mistakes, which the forum did not let me to delete or edit them after 5 minutes. Do you have call of xp_cmdshell in your job? Is it possible that you are trying to use a resource that is not available at time of running Or the user you have set up to run the job (scheduled) does not have the required rights to access the resource? Also what is the wait type in your ssis package? Is it PREEMPTIVE_OS_PIPEOPS? If so, please check the link below: sqlservercentral.com/Forums/Topic802987-110-1.aspx#bm1339542 The links below al
Jan
20
comment sql agent job hangs without any errors or warnings
Do you call of xp_cmdshell in your job? What is the wait type in your ssis package? The links below might be helpful:
Jan
2
comment Transaction and Try-catch in SQL Server Job
Thanks for your helpful answer and fantastic website! However I'm wondering if still I can use this pattern with a simple DML statement (not a stored proc)? Also do we have to save the transaction as below? (I don't have a store proc to use): save transaction usp_my_procedure_name;
Jan
2
comment Transaction and Try-catch in SQL Server Job
Thanks for your reply, could you please give me a hint how to write it to a log?
Dec
28
comment Calling a SQL Server job within another job
Thanks for your reply. It seems we have to go for Option 2. I'm not quite sure whether I understood it correctly though. Do you mean I should create the scripts for both jobs, and then append steps of job2 to end of job1? Is that right?
Dec
27
comment Restore a Database to a New database (T-SQL)
I added 'RECOVERY', but still get the same error.
Dec
10
comment Login failure when running a SSIS package from a SQL Server job
Yes, it is done in ODBC connection, and the test connection can be established successfully there, but I know know how force SQL Server job use that credential, e.g: userId = dba not the SQL server Agent user.
Dec
5
comment error in Execute SQL Task with parameter
It is OLE DB connection, referring to a local database.
Oct
30
comment Calling a .NET method in a SQL Server script
As I had mentioned above the CSharp code provides a method that gets a string, manipulates it based on a complex logic and returns the converted string. This new string will be loaded in the target table. So I get a string (column value) from a source table, run the CSharp method (somehow) and writes the converted value to the target table. The number of source records are to be converted and loaded would be around 20,000.
Oct
30
comment Execute job every time database changes - execute job on each change-event
Could you explain more about the "every time database changes"? Are you referring to data changes in the source tables?
Oct
23
comment Partitioning in SQL Server 2008
Adam, it is a good idea, but for my own benefit, what's gonna happen if the archived data, say a record in data_Feb12 needs to be updated? I think if there is likelihood of having changes on historical data, a process should be in place to facilitate it.
Oct
15
comment Database design: where do I put this data?
Not exactly. Please refer to my edited answer below.
Oct
14
comment Database design: where do I put this data?
Ian, this is the fact based on your scenario. If you aren't happy with it, there is another option: Adding a table which contains the shared columns. This table will sit between the 'Event' and 'Category Event' table. In this table you will have only shared columns.
Oct
14
comment Database design: where do I put this data?
Adding a new table which determines the type,tells you what the type of the Event is, but it does not accommodate the extra columns that are only applicable to one specific Event. Where are the 'Extra Fields' referred in the question stored now?
Oct
12
comment The best way to rename the tables after development completed
We are now at the development phase, but system has almost been completed for that component which these tables are to be renamed. System is now deployed to UAT.
Oct
12
comment The best way to rename the tables after development completed
Thanks for your good answer, but would you be able to advise me what you mean by : "This way you can change the app(s) one at a time (or even one reference at a time), without having to change all of them at once. ". Do you mean we should create the synonym one at the time for example first table t1, do the code changes, then table t2, etc?
Oct
1
comment Closed loops in database model, does it affect the performance?
@Jon: Thanks for your answer, but I don't know what do you mean by 'specific example'? Above is the example of the issue I'm taking about.
Oct
1
comment Closed loops in database model, does it affect the performance?
Therefore to access 'QuestionaireResultsets', you have 2 options: Questionnaires-> Questions-> Answers-> QuestionaireResultsets OR direct join between Questionnaires and QuestionaireResultsets tables.
Oct
1
comment Closed loops in database model, does it affect the performance?
To give you an example, think about this scenario: Table 'Questionnaire is linked to 'Questions', table Questions is linked to 'Answers'. Table 'Answers' is linked to 'QuestionaireResultsets',but 'QuestionaireResultsets' is also linked to 'Questionnaires', which forms a loop.
Sep
17
comment Query performance while using sqlcmd
Thanks. I added "WITH RECOMPILE" to avoid getting stuck in parameter sniffing as suggested in the above post. It would force the sql server not to look for a plan and create one at run time.