Tag Info

New answers tagged

0

Your code for the query is using two servers, identified by IP address as shown below. Where is the server [123.456.7.890]? And where is the server [098.765.4.321]? My guess is that [098.765.4.321] is the [RemoteServer] and [123.456.7.890] is the [LocalServer]. First of all, the slowness of the code is not SQL Agent's fault. Code run from SQL Agent is ...


1

I am reluctant to post this as an answer, because I don't have a tangible example to post with code, but I pointed Mark to Erland Sommarskog's great article, Giving Permissions through Stored Procedures, which has a section on Signing Procedures with Certificates. Cert signing seems to be the right approach for forcing data access through a procedure ...


1

Finally i got it, after several tries i manage to get what i needed, so i'll share my answer. First i made one procedure to update one single row in the column: delimiter // create procedure update_amount_products (in id int) begin update categories set products_amount= (SELECT DISTINCT COUNT( products_to_categories.products_id ) FROM ...


0

Upgrading mySql server is not a fix for this issue. The problem is probably caused by your database name using capital letters on Windows, and windows is case insensitive system. It says in the server options file that you should use 'lower_case_table_names' property set to 2 in case of insensitive file system but even with this setting it still doesn't ...


0

If you are using Windows Authentication in the linked server setup it will not work using the SQL Server agent even if you have Kerberos correctly setup. You can configure the linked server to use SQL authentication for those connections but I would recommend to create a SSIS package for this as the authentication will be easier to configure. Can you ...


1

You are getting that error because you have a row in patient_info where either charges or payment (or both) are NULL. To just make the error go away, you could replace ' ' || c.charges - c.payment || ' ' with ' ' || (c.charges - c.payment) || ' ' but I suppose you would want to handle the case where charges or payment are NULL in some meaningful way, ...


0

Not quite sure what you are trying to achieve bit the UPDATE statement would appear to do the job To update a single known product: UPDATE PRODUCTS SET PRODUCT_AMOUNT = 42 WHERE PRODUCTS_ID = 1 You can update several at once depending on the WHERE clause and you can also set it to a value from another table.


3

It depends on the SQL SECURITY characteristic you defined when you created the procedure. SQL SECURITY for a stored procedure can be either DEFINER or INVOKER. If your user can not create table but the definer of the stored procedure can, then your user will be able to create a table through the stored procedure. By default, the SQL SECURITY characteristic ...


0

Well, many thanks James, Mark, Aaron and especially David Benoit. I read the Joe Sack article you mentioned and it led me to Benjamin Nevarez's article - http://www.benjaminnevarez.com/2011/05/optimizer-statistics-on-linked-servers/. which then pointed out the BOL - Guidelines for Using Distributed Queries. The stored procedure had 3 remote queries. The ...


0

does it make a difference (performance or otherwise) in SQL Server 2014, to run a job step that calls a stored procedure or one that executes inline sql? In your case, since you are just doing reporting, it wont make much difference either using inline SQL or an SP. Aaron Bertrand provides a really good answer to Stored procedures vs. inline SQL


0

Many of the plan options in Profiler are estimated plans, so you will have to use the correct one to get the actual plan. Since you are using SQL Sentry (great product by the way :) ), you should be able to get the plan for the statement run from the Top SQL tab if you check shortly after the job was run. Regardless, IF at all possible, I recommend that ...


1

you could store your result in a Table Variable DECLARE @TableVar TABLE (col1 varchar,col2 int,...) insert into @TableVar exec (Proc1) select Col1 from @TableVar


9

A cursor should very rarely be your first approach. Especially if you've gone to the trouble of making a table type; the primary goal there is to avoid row-by-row processing, splitting strings, etc. Making some guesses about the destination table, its columns, and the name of the column in your TVP, you can insert multiple rows in the destination table by ...


1

Create another Stored Procedure. In it, DECLARE a CURSOR for SELECT id FROM view WHERE another_id=100;. Create a loop; in the loop FETCH one id, then CALL deleteProcedure(id);. (See the manual and online examples for the details.)


0

Following the link that Mark Sinkinson provided, look into converting the 'dd-MMM-yy' using style 0. The dd-MMM-yy format is easily recognized and can be converted. (Check other Styles if you want a variant format.) SELECT CONVERT (DATETIME,'12-Jun-15 13:00:15.123',0); SELECT CONVERT (DATETIME,'12-Jun-15',0); Results are: 2015-06-12 13:00:15.123 ...


1

To be clear, the issue is really with the PRINT command and not the SQLCMD utility. I am guessing that your variable is actually NVARCHAR(MAX), not VARCHAR(MAX) since the PRINT command is limited to only 4000 characters using NCHAR / NVARCHAR. Otherwise it can output up to 8000 characters using NVARCHAR / CHAR. To see that VARCHAR does go beyond 4000 ...


0

Track the activity on your server with one of the off the shelf audit plugins available. McAfee Audit would be my recommendation here sincemyou likely just want to know calls filtered around the stored procs.


0

You could capture the mysqld's uptime or the DB Server's time at the start and end of your code. Perhaps something like this SUGGESTION #1 SELECT variable_value INTO @t1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE variable_name='uptime'; <Your Stored Procedure Code> SELECT variable_value INTO @t2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE ...


3

The CONVERT_IMPLICIT is occurring because you have a collation on the column which does not match the parameter's collation. So the parameter is converted to the column's collation. To explain further - there are collation coercion rules which triggers this conversion. So if you have an implicit collation for the column and a coercible-default for the ...


5

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.


1

You should add 2 more parentheses -- ))) S. Try it!


1

I ended up writing a tool myself. It's available for free download -http://www.gitsql.net I hope it helps other people who want to achieve the same end goal. Here is an article which describes how to source control SQL Server. http://gitsql.net/documentation-04_SQL_Server_and_GIT I've tried to make it as easy as possible. (3 screens) Connect to SQL ...


1

Throw Execute SQL tasks into the control flow and don't link them to each other. Any task without a preceeding task will run asychronously. Executing 20 - 30 Stored procedures in parallel would bring you a lot of workload. I say think twice about it. I have a developer who did that with a custom written windows service, i am facing resorce_semaphore wait ...


1

Instead of SSIS I think you might want to look into SQL Server Service Broker. This lets you send messages asynchronously. See here or here for a start. MSMQ or RabbitMQ may also be options you should consider.


0

As @Rick suggested in the question comments. We need to grant all the permissions, procName expects. In my case, procName Creates and Deletes temporary tables. So, granting CREATE TEMPORARY TABLES permissions on dbName.* did the trick



Top 50 recent answers are included