http://www.windowsitpro.com/authors/author/author/5777727/rsscomment/5777727en-USSun, 27 May 2012 07:41:34 GMTSun, 27 May 2012 07:41:34 GMThttp://www.windowsitpro.com/article/clustering/active-passive-vs-active-active-clustering#commentsAnchorFri, 05 Dec 2008 07:03:44 GMT
I just wish the would be some clarification on licensing if we decide to run 2 instances on just one node for more than 80% of the time.]]>DesperadoFri, 05 Dec 2008 07:03:44 GMThttp://www.windowsitpro.com/article/clustering/active-passive-vs-active-active-clustering#commentsAnchorhttp://www.windowsitpro.com/article/tips/using-t-sql-to-check-database-memory#commentsAnchorMon, 24 Nov 2008 09:24:22 GMT
Shaunt Khaldtiance wrote a stored procedure, usp_SpaceUsedAnalyzer, to keep track of databases’ growth and how much space is being used by each table and index in those databases. This stored procedure extends the functionality of the sp_spaceused system stored procedure to present more detailed information. You can read about the usp_SpaceUsedAnalyzer stored procedure and download its code in the article "Track Database Disk-Space Usage on a Granular Level" at http://sqlmag.com/Articles/ArticleID/100213/100213.html. This article is open to registered users. I hope this stored procedure is what you’re looking for. If not, please let me know. Karen Bemowski, senior editor SQL Server Magazine, Windows IT Pro]]>KarenMon, 24 Nov 2008 09:24:22 GMThttp://www.windowsitpro.com/article/tips/using-t-sql-to-check-database-memory#commentsAnchorhttp://www.windowsitpro.com/article/tips/using-t-sql-to-check-database-memory#commentsAnchorMon, 24 Nov 2008 04:57:41 GMT
By "memory" you mean disk usage. I am interested in finding how to tell which database & tables are consuming memory (not disk).]]>JosephMon, 24 Nov 2008 04:57:41 GMThttp://www.windowsitpro.com/article/tips/using-t-sql-to-check-database-memory#commentsAnchorhttp://www.windowsitpro.com/article/tips/using-t-sql-to-check-database-memory#commentsAnchorMon, 24 Nov 2008 04:56:12 GMT
By "memory" you mean disk.]]>JosephMon, 24 Nov 2008 04:56:12 GMThttp://www.windowsitpro.com/article/tips/using-t-sql-to-check-database-memory#commentsAnchorhttp://www.windowsitpro.com/article/sql-server-2000/transferring-sql-server-registration-information-between-machines#commentsAnchorThu, 19 Jun 2008 14:55:18 GMT
Doesn’t work, only the groups remain, but all empty!]]>DwaineThu, 19 Jun 2008 14:55:18 GMThttp://www.windowsitpro.com/article/sql-server-2000/transferring-sql-server-registration-information-between-machines#commentsAnchorhttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorSat, 07 Jun 2008 03:42:41 GMT
Mialem nadzieje, ze jest jakies inne rozwiazanie tego problemu. I have hope that is other solution for this problem.]]>Zenon`Sat, 07 Jun 2008 03:42:41 GMThttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorhttp://www.windowsitpro.com/article/log-files/choosing-the-right-defragmentation-method#commentsAnchorSun, 25 May 2008 06:23:34 GMT
this is what I was looking for]]>dannySun, 25 May 2008 06:23:34 GMThttp://www.windowsitpro.com/article/log-files/choosing-the-right-defragmentation-method#commentsAnchorhttp://www.windowsitpro.com/article/reporting2/using-dts-to-extract-data-from-multiple-database-systems#commentsAnchorFri, 12 Oct 2007 13:04:50 GMT
Up to now , there has not been a tool made for extracting data out of Oracle that has turned out more efficient by Fast Extract for Oracle distributed by a company called IRI. its benchmarks show the extraction of a 50,000,000 rows table just under 9 minutes. The flat file formats is known to be the most adequate for data transformation and data manipulation.]]>EryckFri, 12 Oct 2007 13:04:50 GMThttp://www.windowsitpro.com/article/reporting2/using-dts-to-extract-data-from-multiple-database-systems#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorWed, 03 Oct 2007 10:34:34 GMT
I was using the Enterprise Manager to change the column size of two columns from varchar(35) and varchar(50) to varchar(65). The table had around 80 columns and 1.5 million rows and it was running for 45 minutes before it stopped responding. I tried the alter table and alter column script in a scheduled sql server agent job and it took just 2 seconds. What a difference!]]>MANOJWed, 03 Oct 2007 10:34:34 GMThttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorhttp://www.windowsitpro.com/article/database-administration/deleting-an-identity-column#commentsAnchorThu, 23 Aug 2007 08:56:40 GMT
what about 2005?]]>ChrisThu, 23 Aug 2007 08:56:40 GMThttp://www.windowsitpro.com/article/database-administration/deleting-an-identity-column#commentsAnchorhttp://www.windowsitpro.com/article/data-modeling/storing-multimedia-data#commentsAnchorTue, 22 May 2007 07:28:54 GMT
the author shoud tell more details about how to store multimedia data.]]>stevetntbomb Tue, 22 May 2007 07:28:54 GMThttp://www.windowsitpro.com/article/data-modeling/storing-multimedia-data#commentsAnchorhttp://www.windowsitpro.com/article/monitoring-and-analysis/tracking-index-usage#commentsAnchorThu, 03 May 2007 14:04:33 GMT
What version of sql is this for? Listing 3 procs do not exist in sql2k.]]>ChipThu, 03 May 2007 14:04:33 GMThttp://www.windowsitpro.com/article/monitoring-and-analysis/tracking-index-usage#commentsAnchorhttp://www.windowsitpro.com/article/monitoring-and-analysis/tracking-index-usage#commentsAnchorThu, 03 May 2007 14:04:33 GMT
What version of sql is this for? Listing 3 procs do not exist in sql2k.]]>ChipThu, 03 May 2007 14:04:33 GMThttp://www.windowsitpro.com/article/monitoring-and-analysis/tracking-index-usage#commentsAnchorhttp://www.windowsitpro.com/article/clustering/active-passive-vs-active-active-clustering#commentsAnchorTue, 17 Apr 2007 05:28:17 GMT
i have an oltp system in which if i configured the sql server in active/active then how the data will be in sync]]>sekhar9 Tue, 17 Apr 2007 05:28:17 GMThttp://www.windowsitpro.com/article/clustering/active-passive-vs-active-active-clustering#commentsAnchorhttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorWed, 11 Apr 2007 10:42:32 GMT
That was the worse answer to someone’s problem by Microsoft ever. I can’t believe I bought a subsricption to this site just for that. Completely misleading to end the article with a ... one word before it actually ends.]]>DanWed, 11 Apr 2007 10:42:32 GMThttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorhttp://www.windowsitpro.com/article/sql-server-2005/the-disappearance-of-sql-server-express#commentsAnchorThu, 29 Mar 2007 07:39:26 GMT
This happened to me also, on two different machines. I had SQLExpress installed; and the instance name was SQL Express. I installed SQL Server 2005 Developer Edition and the SQLExpress instance vanished. There is no services with the name SQLExpress (The only SQL Server service is now SQLServer), and when I use the SQL Server Configuration Manager, there are no objects named SQLExpress. Finally, my app that pointed to .\SQLExpress instance now blows up when I try to run it (though I can easily correct it to point at .)]]>RobKraftThu, 29 Mar 2007 07:39:26 GMThttp://www.windowsitpro.com/article/sql-server-2005/the-disappearance-of-sql-server-express#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/building-dynamic-in-lists#commentsAnchorThu, 08 Mar 2007 14:35:19 GMT
The dynamic SQL is kind of kludgy and opens up a lot of problems and security issues. E.g., SQL injection. A better way is to take the IN list strings and populate a table variable. Then reference the table variable using an IN clause with a sub-select or directly via an INNER JOIN. There are a number of ways to get the list of strings into the stored procedure (right?) that performs the query. 1. A delimited string (e.g., pipe delimited) with a companion function that parses and pivots the string into a table result set. 2. Pass the IN list strings into the procedure as an XML snippet. Then you can use the built-in XML features to pivot the XML into an internal table.]]>JohnThu, 08 Mar 2007 14:35:19 GMThttp://www.windowsitpro.com/article/tsql3/building-dynamic-in-lists#commentsAnchorhttp://www.windowsitpro.com/article/clustering/active-passive-vs-active-active-clustering#commentsAnchorFri, 02 Mar 2007 12:20:37 GMT
I am often asked "Why would you select Active/Active over Active/Passive?". My typical reply is that you can save on hardware resources - provided that each of the servers in the cluster can handle the potential workload in the event of a failure. Since an Active/Passive configuration essentially results in a machine sitting idle waiting for a failure to occur, some DBAs would prefer to put that idle machine to use in an Active/Active configuration. The important thing to note here is that if a failover event occurs, the remaining server will need to service the requests of both SQL Server instances simultaneously - so properly sizing them in the beginning is necessary.]]>LAURAFri, 02 Mar 2007 12:20:37 GMThttp://www.windowsitpro.com/article/clustering/active-passive-vs-active-active-clustering#commentsAnchorhttp://www.windowsitpro.com/article/configuration/preventing-runaway-connection-growth#commentsAnchorWed, 31 Jan 2007 10:44:36 GMT
I’d like to know more about alternatives to using MS’s JDBC driver.]]>DavidWed, 31 Jan 2007 10:44:36 GMThttp://www.windowsitpro.com/article/configuration/preventing-runaway-connection-growth#commentsAnchorhttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorWed, 31 Jan 2007 08:56:35 GMT
test]]>MichaelWed, 31 Jan 2007 08:56:35 GMThttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/if-statements-and-stored-procedure-performance#commentsAnchorFri, 19 Jan 2007 06:36:05 GMT
great information]]>BulentFri, 19 Jan 2007 06:36:05 GMThttp://www.windowsitpro.com/article/tsql3/if-statements-and-stored-procedure-performance#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/if-statements-and-stored-procedure-performance#commentsAnchorThu, 18 Jan 2007 13:16:11 GMT
I found the explanation interesting but it got me wondering about other ’IF’ statements. Will a stored procedure need to be recompiled any time an ’IF’ statement is present in a stored procedure and the branch of the IF statement executed changes based on the IF criteria. In other words, if an IF statement is just setting a value based on another value (i.e. IF @a = 1 then set @b=1 else set @b=0), will the stored procedure still need to be recompiled whenever the value in @a is changed or does a stored procedure only need recompiled if the branches off of the IF statement executes a query?]]>BlaiseThu, 18 Jan 2007 13:16:11 GMThttp://www.windowsitpro.com/article/tsql3/if-statements-and-stored-procedure-performance#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/if-statements-and-stored-procedure-performance#commentsAnchorThu, 18 Jan 2007 12:35:58 GMT
At times I get quite complex SELECT statements, where I feel tempted to put in a big IF statement, instead of using repeated CASE statements, eg SELECT col1, CASE WHEN (col2 = @param1) THEN col3 ELSE col4 END AS ’baa’, CASE WHEN (col2 = @param1) THEN col5 ELSE col6 END AS ’boo’, CASE WHEN (col2 = @param1) THEN col7 ELSE col8 END AS ’buu’, col9 FROM table What is the performance in such a scenario? Also, I had recently a complex 4 times nested case statement and decided to create a new UDF for this and then call it in my procedure’s SELECT statement. Is this good or bad?]]>FelixThu, 18 Jan 2007 12:35:58 GMThttp://www.windowsitpro.com/article/tsql3/if-statements-and-stored-procedure-performance#commentsAnchorhttp://www.windowsitpro.com/article/sql-server-2000/transferring-sql-server-registration-information-between-machines#commentsAnchorFri, 22 Sep 2006 14:17:58 GMT
I would like to know where this is stored in SQL2005. I have a script that trolls registered servers using the registry key mentioned here and generates a list of servers, databases, tables, etc. SQL2005 no longer uses this registry key.]]>KathyFri, 22 Sep 2006 14:17:58 GMThttp://www.windowsitpro.com/article/sql-server-2000/transferring-sql-server-registration-information-between-machines#commentsAnchorhttp://www.windowsitpro.com/article/sql-server-2000/transferring-sql-server-registration-information-between-machines#commentsAnchorTue, 12 Sep 2006 02:34:18 GMT
Here’s a method that’s using SQLDMO to export and import EM-registrations. http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1420 I advise to use this one in stead of just the regedit-copy (which I’ve had some issues with)]]>JohanTue, 12 Sep 2006 02:34:18 GMThttp://www.windowsitpro.com/article/sql-server-2000/transferring-sql-server-registration-information-between-machines#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/easy-reading-see-backup-date-and-time#commentsAnchorTue, 22 Aug 2006 13:41:07 GMT
Nice script, but the code needs a tweak for the date parameter. Currently it needs to use a format style of 12 (instead of 112) or be bumped from 6 to 8 chars long. And the exec statement is missing the close-paren (but I’m just being nit-picky now). :) I’ve written something similar for t-log backups (& alerts that call t-log backups) that creates an output very similar to the "Maintenance Plan" filename output. Myself, I prefer to have the milliseconds added to my timestamps in case an alert fires that creates a backup file, this prevents me from stepping on files previously created via regular backup or another alert call... DECLARE @TimeStamp char(17) SET @TimeStamp = (SELECT REPLACE(CONVERT(varchar(10),getdate(),101),’/’,’)) + (SELECT REPLACE(CONVERT(varchar(12),getdate(),114),’:’,’)) PRINT @TimeStamp Good stuff Gert, keep em coming.]]>GORDONTue, 22 Aug 2006 13:41:07 GMThttp://www.windowsitpro.com/article/tsql3/easy-reading-see-backup-date-and-time#commentsAnchorhttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorTue, 27 Jun 2006 17:16:53 GMT
(note: I only have a few years experience here) I am doing the reverse of a bulk update on the table itself; it may be thought to go to the very table where there is one transaction, that affects the ’bulk insert’: -- "Othertable" is where I am ’mass’ updating, this trigger can be put into something called "ThisTable" -- example: a person deletes a record, it tags associated records, if there is no related table, the "IsOrphan" does not fire, the record is deleted. -- note: NO RELATIONSHIPS in either table INSTEAD of delete AS Declare @Rcount As int Declare @RegID As Int SELECT @rcount = @@rowcount IF @rcount =0 return SELECT @RegID= (SELECT deleted.RegID FROM deleted) BEGIN SET NOCOUNT off; UPDATE OtherTable SET IsOrphan = 1 WHERE (OtherTable.RegID = @RegID)]]>ClaudiaTue, 27 Jun 2006 17:16:53 GMThttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorhttp://www.windowsitpro.com/article/sql-server-2000/importing-word-documents-into-sql-server#commentsAnchorThu, 22 Jun 2006 11:58:44 GMT
Rather sparse details.]]>BillThu, 22 Jun 2006 11:58:44 GMThttp://www.windowsitpro.com/article/sql-server-2000/importing-word-documents-into-sql-server#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/how-not-to-use-use#commentsAnchorSat, 01 Apr 2006 16:18:55 GMT
comment/question that I have are: why do I need to specify WHERE status & 0x40 = 0x40 rather than just WHERE status = 0x40 I know I need the first, because otherwise get no results, but why?]]>BorisSat, 01 Apr 2006 16:18:55 GMThttp://www.windowsitpro.com/article/tsql3/how-not-to-use-use#commentsAnchorhttp://www.windowsitpro.com/article/performance/analyzing-object-i-o-utilization#commentsAnchorWed, 01 Mar 2006 18:01:35 GMT
You didn’t answer his question. What he wants is to know is the amount of IO occuring per object (as set statistics io does), there doesn’t seem to be a way to do this in Profiler.]]>MATTWed, 01 Mar 2006 18:01:35 GMThttp://www.windowsitpro.com/article/performance/analyzing-object-i-o-utilization#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/automatic-truncation-of-long-parameter-values#commentsAnchorWed, 01 Feb 2006 14:45:56 GMT
There behaviour is completely as expected. I dont know why this had to become an "Article". If you try to insert the value into a table you would see the expected error: String or binary data would be truncated. The statement has been terminated. When you assign the value to the variable it doesnt check for the length unless you initiate an action with the variable. when you print the value it truncates to the allowed length, when you insert into a table, it complains.]]>DinakarWed, 01 Feb 2006 14:45:56 GMThttp://www.windowsitpro.com/article/tsql3/automatic-truncation-of-long-parameter-values#commentsAnchorhttp://www.windowsitpro.com/article/reporting2/using-dts-to-extract-data-from-multiple-database-systems#commentsAnchorSat, 12 Nov 2005 00:07:12 GMT
fastreader is at http://www.wisdomforce.com]]>aSat, 12 Nov 2005 00:07:12 GMThttp://www.windowsitpro.com/article/reporting2/using-dts-to-extract-data-from-multiple-database-systems#commentsAnchorhttp://www.windowsitpro.com/article/reporting2/using-dts-to-extract-data-from-multiple-database-systems#commentsAnchorSat, 12 Nov 2005 00:04:07 GMT
You are right about taht. Unloading data from Oracle with DTS is significant slow and adds heavy load on Oracle. We used fastreader from wisdomforce to extract data from Oracle into flat ascii dump files and then loaded with bcp into sql server. fastreader also creates on fly control files for bcp along with the script for load import into sql server]]>aSat, 12 Nov 2005 00:04:07 GMThttp://www.windowsitpro.com/article/reporting2/using-dts-to-extract-data-from-multiple-database-systems#commentsAnchorhttp://www.windowsitpro.com/article/activex/converting-access-data-into-xml#commentsAnchorWed, 09 Nov 2005 16:58:23 GMT
Good]]>JiingyunWed, 09 Nov 2005 16:58:23 GMThttp://www.windowsitpro.com/article/activex/converting-access-data-into-xml#commentsAnchorhttp://www.windowsitpro.com/article/visual-basic-vb/importing-a-word-table-into-sql-server#commentsAnchorWed, 28 Sep 2005 04:39:32 GMT
I need MS-SQL Query to convert the Roww into Column]]>renugopal Wed, 28 Sep 2005 04:39:32 GMThttp://www.windowsitpro.com/article/visual-basic-vb/importing-a-word-table-into-sql-server#commentsAnchorhttp://www.windowsitpro.com/article/permissions/discovering-your-sql-server-user-account#commentsAnchorSat, 24 Sep 2005 11:52:05 GMT
How does this work with 7.0?]]>WILLIAM ESat, 24 Sep 2005 11:52:05 GMThttp://www.windowsitpro.com/article/permissions/discovering-your-sql-server-user-account#commentsAnchorhttp://www.windowsitpro.com/article/sql-server-2000/importing-word-documents-into-sql-server#commentsAnchorThu, 25 Aug 2005 08:52:03 GMT
I’ve created a Word document in VB and I want to store in the database WITHOUT persisting it to the harddrive first, any ideas ?]]>Anonymous User Thu, 25 Aug 2005 08:52:03 GMThttp://www.windowsitpro.com/article/sql-server-2000/importing-word-documents-into-sql-server#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/changing-a-column-s-data-type#commentsAnchorThu, 14 Jul 2005 04:05:48 GMT
Many thanks for a straight answer to a straight question, which worked first time. I can only regret the time I wasted looking elsewhere.]]>Anonymous User Thu, 14 Jul 2005 04:05:48 GMThttp://www.windowsitpro.com/article/tsql3/changing-a-column-s-data-type#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/inserting-data-when-the-column-name-is-a-variable#commentsAnchorWed, 13 Jul 2005 05:14:52 GMT
u can use any thing as a varible even the table name can, thats how the dyanamic tables are manipulated declare @table_name varchar(10), @col_name1 varchar(10), col_name2 varchar(10), @value_01 int, @value_02 varchar(50) set ... -- Gymnastic SQL -------- exec (’INSERT INTO ’+@table_name +’([’+@col_name1+’],[’+@col_name2+’]) values (’’+@value_01+’’,’’+@value_02+’’)’) //-- Gymnastic SQL end ---- hemal hemaldaha@gmail.com ]]>Anonymous User Wed, 13 Jul 2005 05:14:52 GMThttp://www.windowsitpro.com/article/tsql3/inserting-data-when-the-column-name-is-a-variable#commentsAnchorhttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorWed, 13 Jul 2005 02:04:25 GMT
answer 05 january 2005 - rating 5 !!!]]>Anonymous User Wed, 13 Jul 2005 02:04:25 GMThttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/tracking-auto-growing-databases#commentsAnchorMon, 11 Jul 2005 12:44:54 GMT
Here is little better version of dbSize for all dbs: if object_id(’tempdb.dbo.dbSize’) is not null drop table tempdb.dbo.dbSize go create table tempdb.dbo.dbSize ( dbName sysname, dataMB numeric(10,2), logMB numeric(10,2), dbMB numeric(10,2) ) go EXEC sp_MSforeachdb ’INSERT tempdb.dbo.dbSize ( dbName, dataMB, logMB ) SELECT dbName = ’?’, dataMB = ( SELECT Cast (Round((SUM(size)*8.0)/1024.0,2) AS numeric(10,2)) FROM [?].dbo.sysfiles WHERE status & 0x40 <> 0x40 ), logMB = ( SELECT Cast (Round((SUM(size)*8.0)/1024.0,2) AS numeric(10,2)) FROM [?].dbo.sysfiles WHERE status & 0x40 = 0x40 ) ’ update tempdb.dbo.dbSize set dbMB = dataMB + logMB go select dbName=Cast ( dbName AS varchar(30)), dataMB, logMB, dbMB from tempdb.dbo.dbSize -- where dbMB > 1000 order by dbMB desc, dbName go ]]>Anonymous User Mon, 11 Jul 2005 12:44:54 GMThttp://www.windowsitpro.com/article/tsql3/tracking-auto-growing-databases#commentsAnchorhttp://www.windowsitpro.com/article/sql-server-2000/importing-word-documents-into-sql-server#commentsAnchorTue, 21 Jun 2005 07:13:36 GMT
Found what I was looking for (sounds like a song).]]>Anonymous User Tue, 21 Jun 2005 07:13:36 GMThttp://www.windowsitpro.com/article/sql-server-2000/importing-word-documents-into-sql-server#commentsAnchorhttp://www.windowsitpro.com/article/configuration/preventing-runaway-connection-growth#commentsAnchorFri, 17 Jun 2005 10:51:41 GMT
FYI, the fact that Microsoft JDBC Driver is bugged is not breaking news...]]>erbellicoFri, 17 Jun 2005 10:51:41 GMThttp://www.windowsitpro.com/article/configuration/preventing-runaway-connection-growth#commentsAnchorhttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorMon, 13 Jun 2005 12:44:36 GMT
How about an answer for how to do this when set based operations are not an option?]]>Anonymous User Mon, 13 Jun 2005 12:44:36 GMThttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorhttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorFri, 03 Jun 2005 01:48:09 GMT
My case is, I have a SP A in Server-1, and I want to call it from SP B in Server-2. This Server-1 is not registered in Server-2. Hope I’m clear. I’ll be greatful if somebody can suggest me a way out. Can contact me at gvlprasad@gmail.com Thanks a lot.]]>Anonymous User Fri, 03 Jun 2005 01:48:09 GMThttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorhttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorWed, 01 Jun 2005 16:45:34 GMT
Horible article -- doesn’t answer the question at all!]]>Anonymous User Wed, 01 Jun 2005 16:45:34 GMThttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/executing-queries-on-remote-servers#commentsAnchorFri, 27 May 2005 00:03:00 GMT
sankarr@rsi.ramco.com]]>Anonymous User Fri, 27 May 2005 00:03:00 GMThttp://www.windowsitpro.com/article/tsql3/executing-queries-on-remote-servers#commentsAnchorhttp://www.windowsitpro.com/article/stored-procedures/character-limitation-in-sp_executesql#commentsAnchorThu, 19 May 2005 11:23:43 GMT
But given that the OP is wanting to use sp_executesql does your answer which uses exec() do the same thing..?]]>Anonymous User Thu, 19 May 2005 11:23:43 GMThttp://www.windowsitpro.com/article/stored-procedures/character-limitation-in-sp_executesql#commentsAnchorhttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorMon, 09 May 2005 21:27:28 GMT
That is bloody fantastic! You do rock!!]]>Anonymous User Mon, 09 May 2005 21:27:28 GMThttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/building-dynamic-in-lists#commentsAnchorThu, 28 Apr 2005 11:00:36 GMT
This is not abot this article, but about the May 2005 article by Vinod Kumar, which does not bring up a Reader Comments box when you click on it - Where are Figure 1, Figure 2, Figure 3, and the remining Figures, if any? You cannot click on them, nor are they openly displayed in the article.]]>KUMARThu, 28 Apr 2005 11:00:36 GMThttp://www.windowsitpro.com/article/tsql3/building-dynamic-in-lists#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/building-dynamic-in-lists#commentsAnchorThu, 21 Apr 2005 17:13:34 GMT
I will usually use a temp table or table variable. I then parse the @status variable, putting one row at a time in the temp table. I then use the temp table in the "in" statement.]]>Anonymous User Thu, 21 Apr 2005 17:13:34 GMThttp://www.windowsitpro.com/article/tsql3/building-dynamic-in-lists#commentsAnchorhttp://www.windowsitpro.com/article/odbc/what-does-odbc-error-message-mean-#commentsAnchorTue, 05 Apr 2005 04:53:46 GMT
this article is really helpful to know why invalid cursor state error occurs.but it would be more helpful if this article states how to get rid of this error. ]]>Anonymous User Tue, 05 Apr 2005 04:53:46 GMThttp://www.windowsitpro.com/article/odbc/what-does-odbc-error-message-mean-#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/changing-a-column-s-data-type#commentsAnchorTue, 22 Mar 2005 14:14:27 GMT
Alter worked perfect, thanks]]>Anonymous User Tue, 22 Mar 2005 14:14:27 GMThttp://www.windowsitpro.com/article/tsql3/changing-a-column-s-data-type#commentsAnchorhttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorSun, 20 Mar 2005 06:04:52 GMT
oooh... so i’ll to open a cursor ans loop thru... Oracle is smart for this. there is a "for each row" clause.]]>Anonymous User Sun, 20 Mar 2005 06:04:52 GMThttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/tracking-auto-growing-databases#commentsAnchorThu, 10 Mar 2005 12:28:49 GMT
Here’s the version of the script that will enumerate all databases and display their sizes: -- script to enumerate databases -- sedlitz.ca set nocount on set use master DECLARE cur CURSOR FOR SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA OPEN cur DECLARE @dbname sysname print @@servername + ’: ’ + convert(varchar(10),getdate(),101) print ’ ’ FETCH NEXT FROM cur INTO @dbname WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SELECT @dbname = RTRIM(@dbname) -- Do something for each DB print ’DATABASE: ’ + @dbname print ’ ’ -- size is reported in 8K page units exec (’select size*8/1024 as "Size (MB)", maxsize*8/1024 as "Maxsize (MB)", growth, name, filename from ’ + @dbname + ’.dbo.sysfiles’) END FETCH NEXT FROM cur INTO @dbname END CLOSE cur DEALLOCATE cur ]]>Anonymous User Thu, 10 Mar 2005 12:28:49 GMThttp://www.windowsitpro.com/article/tsql3/tracking-auto-growing-databases#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/if-statements-and-stored-procedure-performance#commentsAnchorThu, 03 Mar 2005 18:46:47 GMT
Good because it explains to some extent how execution plan is used and the pro’s and con’s of the options available.]]>ashmiles Thu, 03 Mar 2005 18:46:47 GMThttp://www.windowsitpro.com/article/tsql3/if-statements-and-stored-procedure-performance#commentsAnchorhttp://www.windowsitpro.com/article/sql-server-2000/importing-word-documents-into-sql-server#commentsAnchorWed, 02 Mar 2005 21:52:47 GMT
This is Good]]>Anonymous User Wed, 02 Mar 2005 21:52:47 GMThttp://www.windowsitpro.com/article/sql-server-2000/importing-word-documents-into-sql-server#commentsAnchorhttp://www.windowsitpro.com/article/stored-procedures/character-limitation-in-sp_executesql#commentsAnchorFri, 18 Feb 2005 11:47:53 GMT
Try this declare @Sql_1 nvarchar(4000) declare @Sql_2 nvarchar(4000) : : : declare @Sql_n nvarchar(4000) ...build you statements split over the @Sql_n vars exec(@Sql_1 + @Sql_2 + ....+ @Sql_n) Good Luck]]>Anonymous User Fri, 18 Feb 2005 11:47:53 GMThttp://www.windowsitpro.com/article/stored-procedures/character-limitation-in-sp_executesql#commentsAnchorhttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorFri, 11 Feb 2005 12:56:11 GMT
That’s awesome. You Rock.]]>Anonymous User Fri, 11 Feb 2005 12:56:11 GMThttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/returning-only-the-numeric-part-of-a-string#commentsAnchorFri, 11 Feb 2005 10:01:02 GMT
Sucks.]]>lenny311Fri, 11 Feb 2005 10:01:02 GMThttp://www.windowsitpro.com/article/tsql3/returning-only-the-numeric-part-of-a-string#commentsAnchorhttp://www.windowsitpro.com/article/stored-procedures/character-limitation-in-sp_executesql#commentsAnchorThu, 03 Feb 2005 01:44:56 GMT
Yes, well then lets see the code, otherwise you not helping enybody but your own ego...]]>Anonymous User Thu, 03 Feb 2005 01:44:56 GMThttp://www.windowsitpro.com/article/stored-procedures/character-limitation-in-sp_executesql#commentsAnchorhttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorMon, 31 Jan 2005 21:32:02 GMT
test]]>Anonymous User Mon, 31 Jan 2005 21:32:02 GMThttp://www.windowsitpro.com/article/development/firing-a-trigger-for-each-inserted-row#commentsAnchorhttp://www.windowsitpro.com/article/sql-server/evaluating-the-page-splits-sec-value#commentsAnchorMon, 24 Jan 2005 15:18:45 GMT
Very informative..thx]]>Anonymous User Mon, 24 Jan 2005 15:18:45 GMThttp://www.windowsitpro.com/article/sql-server/evaluating-the-page-splits-sec-value#commentsAnchorhttp://www.windowsitpro.com/article/sql-server-2000/text-in-row-vs-varchar-or-text#commentsAnchorSun, 23 Jan 2005 20:06:02 GMT
This helped a lot. Thanks!!!]]>Anonymous User Sun, 23 Jan 2005 20:06:02 GMThttp://www.windowsitpro.com/article/sql-server-2000/text-in-row-vs-varchar-or-text#commentsAnchorhttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorWed, 05 Jan 2005 04:43:50 GMT
Try this: CREATE TABLE #locks (spid int, dbid int, objid int, objectname sysname NULL, indid int, type char(4), resource char(15), mode char(10), status char(6)) INSERT #locks (spid, dbid, objid, indid, type, resource, mode, status) EXEC dbo.sp_lock SELECT * FROM #locks DROP TABLE #locks]]>Anonymous User Wed, 05 Jan 2005 04:43:50 GMThttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorhttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorTue, 04 Jan 2005 08:53:14 GMT
Is this possible? -- Pputting a result of Stored procedure (sp-1) into a temp table (#t) in a new Stored procedure (sp-new) If yes, pls give SQL for this...]]>Anonymous User Tue, 04 Jan 2005 08:53:14 GMThttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/changing-a-column-s-data-type#commentsAnchorThu, 30 Dec 2004 12:19:45 GMT
If the table is involved in declarative relationships, stored procedures, functions, triggers and so forth - alter table is the only realistic option isn’t it? ]]>Anonymous User Thu, 30 Dec 2004 12:19:45 GMThttp://www.windowsitpro.com/article/tsql3/changing-a-column-s-data-type#commentsAnchorhttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorWed, 22 Dec 2004 21:43:38 GMT
Your Comments: : ]]>Anonymous User Wed, 22 Dec 2004 21:43:38 GMThttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorhttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorTue, 07 Dec 2004 09:11:45 GMT
The question was: "How do I call stored procedure B from within stored procedure A?" You provided a handy tip for the project he happens to be working on, but you didn’t answer the question that he needed answered.]]>Anonymous User Tue, 07 Dec 2004 09:11:45 GMThttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorWed, 01 Dec 2004 06:50:27 GMT
Thank you - I learnt from this...]]>Anonymous User Wed, 01 Dec 2004 06:50:27 GMThttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorhttp://www.windowsitpro.com/article/stored-procedures/character-limitation-in-sp_executesql#commentsAnchorWed, 24 Nov 2004 07:34:27 GMT
This isn’t true. A developer and I have put together multiple 8000 varchar values and executed them with sp_executesql]]>Anonymous User Wed, 24 Nov 2004 07:34:27 GMThttp://www.windowsitpro.com/article/stored-procedures/character-limitation-in-sp_executesql#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorTue, 23 Nov 2004 23:25:18 GMT
Hold the Phone! The column being changed is a varchar. The data wont be physically changed. The operation wont need to log anything and will run in a few seconds (assuming size increase, not decrease). A char(10) to char(80) change would be a huge logging op. It just goes to show how daft Enterprise Manager can be, rebuilding an entire table just to change the max size of a variable length column! ]]>Peter Ellis Tue, 23 Nov 2004 23:25:18 GMThttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorMon, 22 Nov 2004 16:15:36 GMT
You could BCP the table out, truncate the table, modify the column size then BCP the table back in. Logging can be turned off.]]>WillMon, 22 Nov 2004 16:15:36 GMThttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorFri, 19 Nov 2004 14:27:56 GMT
I think you also need to consider whether the time taken for the operation or the amount of Log space consumed is the most critical in your environment. I think the Backup, set Simple Recovery, ALTER TABLE, set Full recovery, Backup again approach will probably be fastest and have least user impact. However, if you really have an issue with the disk space and can live with some downtime then writing your own script/Job to switch to Simple Recovery, create a new table, transfer all the data, drop the original and rename the new one, finally switching back to Full recovery may be preferable. (Don’t forget to allow time for Full backups before and after this). Although this is the same process that EM would use, by writing your own script you can transfer the data in smaller, batched transactions. Even in Simple Recovery activity is still logged. The difference is that Simple recovery truncates the log at each checkpoint. Log entries since the start of the oldest incomplete transaction cannot be truncated, but breaking the activity into smaller transactions allows truncation of the completed transactions, so consuming less space overall. The biggest downside of that, however, is that you will really need downtime during the transfer process because neither table has the full data that may be needed by the users until the entire transfer is complete.]]>PeterFri, 19 Nov 2004 14:27:56 GMThttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorFri, 19 Nov 2004 08:22:21 GMT
Why won’t you create a new table per your requirements, and then insert rows from old to new? You can also commit transactions per half a million records to limit log size growth]]>Anonymous User Fri, 19 Nov 2004 08:22:21 GMThttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorFri, 19 Nov 2004 07:03:16 GMT
I think you’ll need to turn off logging, and ensure that nothing else accesses this table at the time you’re doing this operation--unless you can be certain that none of these processes alter data. Still, in general, it’s best to do massive operations like this with the database fairly isolated. You can still run the alter table command outlined above, and if you want to run this as a scheduled SQL Server Agent task, I’d recommind putting backup database DBx to disk = ’path\file’ with init command in front, followed by alter database DBx set recovery simple Then issue your alter table statement, followed by alter database DBx set recovery full and then backup database DBx to disk = ’path\file’ with noinit This way, transaction logging can continue normally afterwards, and you’ll have a single file with a before and after snapshot of the database. ]]>Anonymous User Fri, 19 Nov 2004 07:03:16 GMThttp://www.windowsitpro.com/article/tsql3/resizing-a-column-in-a-large-table#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/tracking-auto-growing-databases#commentsAnchorThu, 11 Nov 2004 19:12:03 GMT
I’ve thought about using SQL Server 2000’s Profiler to script a trace to track the relevant events. These events occur sufficiently infrequently that overhead ought to be acceptable. We could use sp_ProcOption to make exec the script on re-start. We’d have to create some mechanism to make sure to create a different file name on re-start (say, using GetDate()). Better yet would be to write the results directly to table; however, I’d have to noodle this a bit before knowing whether that would be simple to automate. ]]>JimmyThu, 11 Nov 2004 19:12:03 GMThttp://www.windowsitpro.com/article/tsql3/tracking-auto-growing-databases#commentsAnchorhttp://www.windowsitpro.com/article/quering/archiving-user-activity#commentsAnchorFri, 29 Oct 2004 10:26:25 GMT
I have a simular datanbase, I broke down the archive to a Daily Database (hold 3 days), a Monthly Database (holds 33 days) and a yearly database (designed to hold 5 years). All database have the same strucutre and I use a DTS process along with SP’s to move the data between the databases. We then use the new Reporting Services to front the database have about 50 canned reports. We only allow ad-hoc querry on the daily database. If the ad-hoc querry needs to run on the other database, a new report is written and added to the report library.]]>KURTFri, 29 Oct 2004 10:26:25 GMThttp://www.windowsitpro.com/article/quering/archiving-user-activity#commentsAnchorhttp://www.windowsitpro.com/article/data-modeling/storing-multimedia-data#commentsAnchorFri, 10 Sep 2004 08:13:04 GMT
Please, would you put an example?]]>UlisesFri, 10 Sep 2004 08:13:04 GMThttp://www.windowsitpro.com/article/data-modeling/storing-multimedia-data#commentsAnchorhttp://www.windowsitpro.com/article/database-administration/deleting-an-identity-column#commentsAnchorThu, 09 Sep 2004 15:49:56 GMT
Your question and answer are confusing each other. You can certainly drop a column that has the identity property. I think your question meant to read, "Can I use a T-SQL script to remove the IDENTITY property for a column?"]]>yukonguyThu, 09 Sep 2004 15:49:56 GMThttp://www.windowsitpro.com/article/database-administration/deleting-an-identity-column#commentsAnchorhttp://www.windowsitpro.com/article/log-files/bulk-loading-data-into-sql-server-2000#commentsAnchorTue, 31 Aug 2004 17:59:46 GMT
The BCP command in the article omits the table lock load hint. It should read as : BCP.EXE testdb.dbo.sales IN f:\data\sales.char -S (local)\dev -T -c -b 10000 -h "tablock" ]]>Peter Ellis Tue, 31 Aug 2004 17:59:46 GMThttp://www.windowsitpro.com/article/log-files/bulk-loading-data-into-sql-server-2000#commentsAnchorhttp://www.windowsitpro.com/article/log-files/bulk-loading-data-into-sql-server-2000#commentsAnchorTue, 31 Aug 2004 01:22:22 GMT
It’s a shame that you dont show the charts that appear in the print article. ]]>Peter Ellis Tue, 31 Aug 2004 01:22:22 GMThttp://www.windowsitpro.com/article/log-files/bulk-loading-data-into-sql-server-2000#commentsAnchorhttp://www.windowsitpro.com/article/performance/analyzing-object-i-o-utilization#commentsAnchorTue, 03 Aug 2004 16:19:41 GMT
I would like to change my user id and password. How do I do that? my email address is sgw@inel.gov]]>SharolTue, 03 Aug 2004 16:19:41 GMThttp://www.windowsitpro.com/article/performance/analyzing-object-i-o-utilization#commentsAnchorhttp://www.windowsitpro.com/article/quering/finding-login-counts-and-creation-dates#commentsAnchorTue, 03 Aug 2004 14:00:10 GMT
handy]]>dnguyen27 Tue, 03 Aug 2004 14:00:10 GMThttp://www.windowsitpro.com/article/quering/finding-login-counts-and-creation-dates#commentsAnchorhttp://www.windowsitpro.com/article/tsql3/inserting-data-when-the-column-name-is-a-variable#commentsAnchorMon, 21 Jun 2004 07:17:51 GMT
this is very good but unfortunately it not solved my problem i want to use the variable as column name in the loop here is my problem when i used the intialized the vriable in the select statemnt ,it received correctly column name in the varibale but in update statement it not gives the actaul value but again the name of column,i want an immediate response from you thanks ver much here is my procedure //////////////////////////////////////////////////////// CREATE PROCEDURE dbo.call_zone as declare @v_code1 varchar(10),@v_code2 varchar(10),@v_counter numeric,@v_test1 varchar(15),@v_test2 varchar(15); DECLARE emp3 CURSOR FOR SELECT code1,code2,counter FROM feb04_dtli_guj; BEGIN OPEN emp3; FETCH next from emp3 INTO @v_code1,@v_code2,@v_counter; while @@fetch_status = 0 Begin -- print @v_code1; -- print @v_counter; set @v_test1 = (select title from lhr_codes_original where city_code = @v_code2) update feb04_dtli_guj set call_zone=(select @v_test1 from lhr_codes_test where city_code = @v_code1) where counter=@v_counter and call_zone is null; FETCH next from emp3 INTO @v_code1,@v_code2,@v_counter; ENd CLOSE emp3 DEALLOCATE emp3 end GO ////////////////////////////////////////////////////////]]>mudassarMon, 21 Jun 2004 07:17:51 GMThttp://www.windowsitpro.com/article/tsql3/inserting-data-when-the-column-name-is-a-variable#commentsAnchorhttp://www.windowsitpro.com/article/sql-server/evaluating-the-page-splits-sec-value#commentsAnchorThu, 20 May 2004 12:29:35 GMT
Useful!]]>FARUKThu, 20 May 2004 12:29:35 GMThttp://www.windowsitpro.com/article/sql-server/evaluating-the-page-splits-sec-value#commentsAnchorhttp://www.windowsitpro.com/article/log-files/bulk-loading-data-into-sql-server-2000#commentsAnchorThu, 06 May 2004 10:55:26 GMT
Confused! Point 4 says to ensure the table has NO indexes to enable minimal logging, yet then this sentence appears: "When you’re loading into a table that contains a clustered index, determining whether you’re using minimal logging is more difficult." If point 4 is right, it can’t use minimal logging. But the results seem to imply minimal logging is possible with a clustered index, thus proving point 4 wrong. This is a VITAL point to clarify. I’m currently dropping clustered indexes to get minimal logging, but I think it’s hurting performance to do so. Should I leave the clustered indexes in place?]]>Vince Iacoboni Thu, 06 May 2004 10:55:26 GMThttp://www.windowsitpro.com/article/log-files/bulk-loading-data-into-sql-server-2000#commentsAnchorhttp://www.windowsitpro.com/article/log-files/appending-an-identity-column-to-a-temporary-table#commentsAnchorFri, 02 Apr 2004 13:08:18 GMT
I just got something new for me, and it will help me if need to use it. Thank you.]]>Boris Shimonov Fri, 02 Apr 2004 13:08:18 GMThttp://www.windowsitpro.com/article/log-files/appending-an-identity-column-to-a-temporary-table#commentsAnchorhttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorFri, 19 Mar 2004 16:31:52 GMT
I don’t see how this shows you how to insert the results of one stored proc into another. The example is only using select statements? It doesn’t really answer the question asked.]]>Yvonne Kaeder Fri, 19 Mar 2004 16:31:52 GMThttp://www.windowsitpro.com/article/user-defined-function-udf/calling-a-stored-procedure-from-another-stored-procedure#commentsAnchorhttp://www.windowsitpro.com/article/permissions/replicating-stored-procedure-changes-and-permissions#commentsAnchorFri, 31 Oct 2003 08:59:52 GMT
Just what I was looking for. I had setup Snapshot replication and wasn’t sure how to GRANT permissions to the stored procedure. This article pointed me directly to the source and saved me a lot of time. Thanks]]>David Washington Fri, 31 Oct 2003 08:59:52 GMThttp://www.windowsitpro.com/article/permissions/replicating-stored-procedure-changes-and-permissions#commentsAnchorhttp://www.windowsitpro.com/article/stored-procedures/adding-columns-to-replicated-tables#commentsAnchorTue, 28 Jan 2003 13:11:04 GMT
This is in regards to your article on the Feb 2003 Issue for InstantDoc #37513 which is "Adding Columns to Replicated Tables". After reading up on this in MS SQL Books online, they claim that the subscriber will not get updated if you add a new column using this command as mentioned below. Where as your article says that it will automatically add the column to the subscriber.Can someone validate this since it is not documented by Microsoft and whether this was updated thru a service pack. Read the Excerpt from SQL Books Online as pasted below: Remarks sp_repladdcolumn is used for all types of replication. When using sp_repladdcolumn, if a schema change is made to an article that belongs to a publication that uses a DTS package, the schema change is not propagated to the Subscriber, and the custom procedures for INSERT/UPDATE/DELETE are not regenerated on the Subscribers. The user will need to regenerate the DTS package manually, and make the corresponding schema change at the Subscribers. If the schema update is not applied, the Distribution Agent may fail to apply subsequent modifications. Before making a schema change, make sure there are no pending transactions to be delivered. For more information, see How Transforming Published Data Works. Timestamp and computed columns will be filtered out for character mode publications. If adding a timestamp or computed column using sp_repladdcolumn, subscriptions of such publications will not receive this new column. Important A backup of the publication database should be performed after sp_repladdcolumn has been executed. Failure to do so can cause a merge failure after a restore of the publication database.]]>Ravi Nanda Tue, 28 Jan 2003 13:11:04 GMThttp://www.windowsitpro.com/article/stored-procedures/adding-columns-to-replicated-tables#commentsAnchorhttp://www.windowsitpro.com/article/sql-server/confining-an-incremental-counter-value-to-one-table#commentsAnchorSun, 01 Dec 2002 14:43:38 GMT
I can’t see how this returns a the next unique number to use for your insert. Dosen’t it just re-seed all the values in the column by an increment of 1? I’m guessing that mycounter is the table name and C1 is the column. Could somebody show me the light?]]>BrendonJ Sun, 01 Dec 2002 14:43:38 GMThttp://www.windowsitpro.com/article/sql-server/confining-an-incremental-counter-value-to-one-table#commentsAnchor