SQL Server 2012 :: Replication - Missing Stored Procedure Comment Header
Jul 25, 2013
I have recently started using replication in SQL 2012 SP1. When a stored procedure is altered on the source, the changes are replicated to the subscribers; however, the comment headers are removed at the subscribers. Due to the vast number of stored procedures I have, I do not want to move the comments below the Create Procedure statement. Are there any other ways to have comment header move with the stored procedures?
Here is what I am experiencing
Source SP
ALTER PROCEDURE [dbo].[SPTest]
AS
BEGIN
SELECT GETDATE()
END
Destination SP
ALTER PROCEDURE [dbo].[SPTest]
AS
BEGIN
SELECT GETDATE()
END
View 7 Replies
ADVERTISEMENT
Jun 23, 2006
Hi,Is there anyone encountered this error before & how it is being resolved?[Microsoft][ODBC SQL Server Driver][SQL Server]Missing end comment mark '*/'The error pops-up when I was running a DTS Import/Export from a SQL server(source) to another SQL server (destination) residing on a differentmachine. I'm copying all tables, views, & stored procedures of a database.Thanks for any input.Regards,Maricel
View 1 Replies
View Related
Mar 17, 2015
I have a SSRS report developed in SQL 2012. When I exporting the report into word the back ground colour of page header missing. When Exporting to PDF and Excel page header BG colour showing.
View 0 Replies
View Related
Sep 26, 2014
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
View 3 Replies
View Related
Jul 20, 2005
Hai friendsCould u pleas give me some noites on replication . I worked out thruwizards but not succeded.With thanksRaghu
View 1 Replies
View Related
Jun 30, 2007
Hi
I am stuck while doing synchronization between SQL Server 2000 and SQL Server CE 2.0.
getting an Error "Header information is either corrupted or missing."
My Specification is follow :
Windows XP with SP2
Visual Studio 2005 Enterprise edition
SQL Server 2000 (SP3a)
SQL Server CE 2.0
Application is Smart Device application (Pocket PC 2003) using C#.
Microsoft ActiveSync 4.5
IIS 5.1
I followed steps as per given in this URL :
http://msdn2.microsoft.com/en-us/library/ms839425.aspx#sql_serverce_replication_net_topic2I performed eeach steps successfully, My code is as per given below.
private void button1_Click(object sender, EventArgs e)
{
getSyncReady();
}
SqlCeReplication loSqlCeReplication;
private void getSyncReady()
{
try
{
String lsDBFile = "Northwind.sdf";
loSqlCeReplication = new SqlCeReplication();
loSqlCeReplication.InternetUrl = "http://192.168.0.5/Northwind/sscesa20.dll";
loSqlCeReplication.Publisher = "C99";
loSqlCeReplication.PublisherSecurityMode = SecurityType.NTAuthentication;
loSqlCeReplication.Publication = "NorthwindProducts";
loSqlCeReplication.PublisherDatabase = "Northwind";
loSqlCeReplication.Subscriber = Dns.GetHostName();
loSqlCeReplication.SubscriberConnectionString = "Data Source=" + lsDBFile;
if (!System.IO.File.Exists(lsDBFile))
{
MessageBox.Show("The application requires synchronization", "Replicator",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
loSqlCeReplication.AddSubscription(AddOption.CreateDatabase);
}
loSqlCeReplication.Synchronize();
}
catch (Exception foException)
{
throw foException;
}
}
I am not getting whats going wrong.
My Firewall is disable and i have not installed any antivirus softwares (including Norton internet Security....in many forums i got this suggestion but i think its not an issue,even though for solving this issue i performed new installation of Windows XP SP-2 )
View 4 Replies
View Related
Jan 21, 2014
On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.
The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.
For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.
I changed the procdure to do nothing (return 1 in first line).
So with all parameters set from
command.ExecuteNonQuery()to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end
it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.
When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)
SP:StmtCompleted -- Encrypted Text.
As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.
View 5 Replies
View Related
May 30, 2006
We are using SQL Server 2005 to develop a simple SP. We started by including an output parameter which would report back the identity of the record being inserted or updated. We have since been trying to drop and recreate the SP without the output parameter, or alter the SP with the same outcome in mind. Neither has been succeeding, as confirmed by inspection of the sys.objects and sys.parameters tables. What might we be missing? We are using the Developer Edition, which may or may not be adequate to the task. Or maybe earlier versions of SQL Server are more robust and would be more successful to help us succeed? Please advise. Thank you.
View 5 Replies
View Related
Feb 9, 2006
SQL Server 2000, VB.NET 1.1.
Here's the dummy sp:
CREATE PROCEDURE am_user.spTestCrap ( @maxFloodControl int, @int_memID int) ASset nocount onselect @maxFloodControl, @int_memIDreturn 62GO
Here's some VB.NET code that does not work:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strConn As String = "server=amdb1;UID=am_User;PWD=thepwd;DATABASE=thedb;pooling=true"
Dim objConn As New SqlClient.SqlConnection(strConn) objConn.Open() Dim objComm As SqlClient.SqlCommand Dim nReturnedValue As Integer Dim memID As Integer
'This is a "root" level message objComm = New SqlClient.SqlCommand("am_User.spTestCrap", objConn) objComm.CommandType = CommandType.StoredProcedure
objComm.Parameters.Add("@RETURN_VALUE", SqlDbType.Int) objComm.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
objComm.Parameters.Add("@maxFloodControl", CType(0, Integer))
objComm.Parameters.Add("@int_memID", memID)
Try
objComm.ExecuteNonQuery()
If Not IsDBNull(objComm.Parameters("@RETURN_VALUE").Value) Then nReturnedValue = CType(objComm.Parameters("@RETURN_VALUE").Value, Integer) End If Catch MsgBox(Err.Description) End Try End Sub
The error I get back in the message box is: "Procedure 'spTextCrap' expects parameter @maxFloodControl", which was not supplied."
Why the BLEEP not? Didn't I supply it? What the BLEEP am I missing?
TIA,
View 4 Replies
View Related
Nov 10, 2006
anyone know where the stored procedure wizard went in sql 2005?
in 2000 it was so easy to check the type of SP you wanted and then it automatically wrote all the fields and value tsql for that - literally took 1 minute to create an insert update delete stored proc on any table.
i don't see that in sql 2005
let me know, if you know.
thanks - Jeff
View 1 Replies
View Related
Sep 21, 2001
I have installed Standard Sql 2000 .
However i don't find the ex stored Procedures xp_sqlinventory and sp_sqlregister ..
Can anyone Point out here i can get them?
Thanks
View 1 Replies
View Related
Oct 24, 2014
I'm updating some tables in a subscriber database with a stored procedure. After the tables get updated I'd like to sync them with the other subscriber dbs and the publisher db in that same stored procedure.I can do it manually in SSMS with the View Synchronization method. Are my only alternatives a batch job or C#?
View 1 Replies
View Related
Jul 29, 2015
I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:
IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name
[Code] ....
I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:
ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)
How can I change my code to workaround this? Perhaps some explicit transactions?
View 6 Replies
View Related
Mar 3, 2015
We need to create a pdf file from SQL server preferably from a stored procedure. Application will call the stored procedure and it should generate pdf. From my research it appears it can be done using various external tools with licensing/costs. But is it possible to do this within sql server database without additional costs? I read that this can be done by SSRS in SQL server but not sure if it is a good solution and if it is additional licensing..
View 3 Replies
View Related
Sep 21, 2015
Is it possible to pass a sum of vars to a SP ?I've tried this but it gives me an error
exec mysp
@param = (@var1 + @var2)
View 1 Replies
View Related
May 16, 2008
I've got a field that might have spurious values in it (say, an admin adds a new row but doesn't have an entry for this field).
I'm trying to swap in the string no_image_EN.jpg if the value in the db does NOT end in .jpg. That way, any value rreturned is either a valid filename or no_image
I'm having trouble with the CASE statement, particularly testing just the last few cahracters of the string:
select product_code,
CASE can_image_en
?? When (can_image_en LIKE '%.jpg') then can_image_en
Else 'no_image_EN.jpg'
End as can_image_en,
none of these do the trick either (some are bad syntax obviously):
? When (can_image_en LIKE '%.jpg') then can_image_en
? When LIKE '.jpg' then can_image_en
? When '%.jpg' then can_image_en
? When right(can_image_en,4) = '%.jpg' then can_image_en This is the one that has correct syntax, though it seems to return false in ALL cases CASE can_image_en
When '%.jpg%' then can_image_en
Else 'no_image_EN.jpg'
View 5 Replies
View Related
Dec 9, 2013
After my Stored Procedure has run, I need to call [URL] ..... file. Can this be done from my Stored Procedure?
View 2 Replies
View Related
Dec 17, 2013
I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.
What would be the most proficient way of achieving this?
I had initially looked at using IF, TRY ie:
IF @SearchField= 'order_reference'
BEGIN TRY
select data
from mytables
END TRY
However I'm not sure this is the most efficient way to handle this.
View 2 Replies
View Related
Jun 27, 2014
is it possible to create PW on Stored Procedure? No one can execute or Alter any Store Procedure with Password?
View 1 Replies
View Related
Jul 26, 2014
I need to create a procedure with Restore Database command in many applications in differente platforms. But I can't "use master". How do I do it?
View 1 Replies
View Related
Jan 22, 2015
I have a stored procedure that calls several views that rely on each other. In the past these views used to go parallel and use up all 100% of the CPU (12 cores), and now when the same stored procedure runs it only uses 8% of the CPU (1 core). This extends the time spent on the query from roughly 10-15 sec to 2-3min. I'm not quite sure why this is happening.
Are there some obvious things to look at when optimizing views to utilize all cores/threads? Also, it doesn't matter if I set Cost Threshold for Parallelism to 1 or 50 or 5, it is always the same, and I have Max Degree of Parallelism set to 0 as well, which should mean to use all cores when available.
View 9 Replies
View Related
Feb 14, 2015
I have a table with the list of all TableNames in the database. I would like to query that table and find any tables used in any stored procedure in that DB.
Select * from dbo.MyTableList
where Table_Name in
(
Select Name
From sys.procedures
Where OBJECT_DEFINITION(object_id) LIKE '%MY_TABLE_NAME%'
Order by name
)
View 7 Replies
View Related
Feb 26, 2015
Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.
The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.
Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?
View 6 Replies
View Related
Mar 30, 2015
In a t-sql 2012 stored procedure, I would like to know how I can complete the task I am listing below:
In an existing t-sql 2012 stored procedure, there is a table called 'Atrn' that is truncated every night. The Table 'Atrn' has a column called 'ABS' that is populated with incorrect data.
The goal is to place the correct value into 'ABS' column that is located in the Atrn table while the t-sql 2012 stored procedure is excuting.
**Note: The goal is to fix the problem now since it is a production problem. The entire stored procedure that updates the 'dbo.Atrn' table will be rewritten in the near future.
My plan is to:
1. create a temp table called '#Atrnwork' that will contain the columns called,
Atrnworkid int, and ABSvalue with a double value.
2. The value in the column called Atrnworkid in the '#Atrnwork' table, will obtain its value from the key of the 'Atrn' called atrnid by doing a select into. At the same time, the value for ABSvalue will be obtained by running some sql when the select into occurs?
3. The main table called 'Atrn' will be changed with a update statement that looks something like:
Update Atrn
set ABS = ABSvalue
join Atrn.atrnid = #Atrnwork.Atrnworkid
In all can you tell me what a good solutiion is to solve this problem and/or display some sql on how to solve the problem listed above?
View 5 Replies
View Related
Jul 21, 2015
I have made this defination for a stored procedure:
PROCEDURE EP_Conterbalances
@Start_Date_For_Totals_Date DATETIME,
@EmpFilterAddDuty VARCHAR(500),
@CounterBalanceType_id INT,
@dateFrom DATETIME,
[Code] .....
The value of @EmpFilterAddDuty could be:
'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID '
If i Replace @EmpFilterAddDuty with this in a QUERY, it gives me the expected result, but if i try to execute the stored procedure.:
DECLARE@return_value int
EXEC@return_value = [dbo].[EP_Conterbalances]
@Start_Date_For_Totals_Date = N'20120831',
@EmpFilterAddDuty = 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B
[Code] .....
I get this error code:
Conversion failed when converting the varchar value 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ' to data type int.
I really do not understand why SQL 2012 tries to convert the value to an int, and I want to know how to pass the text string.
View 4 Replies
View Related
Jul 29, 2015
What is the purpose of creating table variable and inserting data into it and selecting from table variable inside of stored proc?
Why can't the stored proc just have select statement instead of creating table variable?
View 3 Replies
View Related
Sep 16, 2015
Our developers have gotten this idea lately that instead of having many small stored procedures that do one thing and have small parameter lists that SQL can optimize query plans for, its better to put like 8-10 different queries in the same stored procedure.
They tend to look like this:
create procedure UberProc (@QueryId varchar(50))
as
if @QueryId = 'First Horrible Idea'
begin
select stuff from something
end
if @queryid = 'Second really bad idea'
begin
select otherstuff from somethingelse
end
I see the following problems with this practice:
1) SQL can't cache the query plan appropriately
2) They are harder to debug
3) They use these same sorts of things for not just gets, but also updates, with lots of optional NULLable parameters that are not properly handled to avoid parameter sniffing.
View 9 Replies
View Related
Jul 10, 2007
Hello, I am getting many of these messages in my server's event log (approximately 13 every 5 seconds or so). I have tried clearing the queue with "END CONVERSATION @ConvHandle WITH CLEANUP;" but the event log keeps getting messages. I have attached an example below.
Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9724
Date: 7/10/2007
Time: 3:52:37 PM
Description:
The activated proc [dbo].[SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8] running on queue HoudiniPlatform.dbo.SqlQueryNotificationService-32e779eb-edcb-44d1-ba30-93f46ef9d9f8 output the following: 'Could not find stored procedure 'dbo.SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8'.'
View 3 Replies
View Related
Nov 21, 2013
I have this SP
ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int)
AS
DECLARE @steps bigint
SET @steps = DATEDIFF(hour, @start, @stop)/ @step
DECLARE @i bigint
SET @i=0
[Code] ....
View 1 Replies
View Related
Jan 6, 2014
I have a SQL 2012 database that has 10 tables. One of the tables is populated by manual import from CSV file. Each time a user calls custom ASP.NET code., records get inserted into a table called forecast_data with incremental increase in FileID. So first import has FileID of 1, second import has FileID of 2 etc.
Structure:
TABLE [dbo].[forecast_data](
[recID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [int] NULL,
[Project_Name] [nvarchar](255) NULL,
[Stoplight_Status] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[Region] [nvarchar](255) NULL,
[code]....
What I am trying to do is only keep the data that has the highest FileID (MAX(FileID). I would like to write a store procedure that removes all older data once a new import is written into the table.
View 6 Replies
View Related
Feb 17, 2014
I have a stored procedure that ends with
Select columnname from tablename order by ordercolumn
We will call that "sp_foldersOfFile". It takes 1 parameter, a fileID (int) value.
The result when I execute this from within Management Studio is a single column of 1 to n rows. I want to use these values in another stored procedure like this:
Select @userCount = COUNT(*) from permissions where UserID = @userID and
(projectid = @projectID or projectid=0) and
clientid = @clientID and
folderpermissions in (dbo.sp_FoldersOfFile(@fileID))
The Stored Procedure compiles but it does not query the folderpermissions in the selected values from the sp_FoldersOfFile procedure. I'm sure it is a syntax issue.
View 9 Replies
View Related
May 14, 2014
I have table named TEMPLATE_ACTIVITY. This is template table I have 27 this kind of tables.
I want to create stored procedure to change name MICHELIN_US_ instead of TEMPLATE_ all remaining name should be same. For that I am using 'Create Table As Select' to keep same structure as Template tables.
I want to create sp as like execute this way Exec @MICHELIN_US_
So that in future if Client change to MICHELIN_US_ to UNITED_ I can just change Exec @UNITED_
And it will change all table names to UNITED_ACTIVITY
I want to create this SP for different client.
View 3 Replies
View Related
Jul 9, 2014
SQL Server 2012 Standard SP 1.
Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:
declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;
Print statement prints @NumBufferManagerRows as 0.
Here is the called stored procedure B:
CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT
[Code] ...
View 2 Replies
View Related
