Stored Procedure Won't Work After Migrated To Sql 7.0
Sep 28, 2000Do anybody aware of any problems with the way that sql 7.0 converting stored procedures from sql 6.5...thanks
View 1 Replies
ADVERTISEMENT
Migrated From SQL 2k5 Express To SQL Server - Profile Doesn't Work
Aug 22, 2006I recently created an application that stores 3 variables in the web.config in the "Profile"configuration:
<profile>
<properties>
<add name="Department" defaultValue="" />
<add name="UserName" defaultValue="Anonymous" allowAnonymous="true"/>
<add name="AL" defaultValue="" />
</properties>
</profile>
By doing this my web.config automatically configured itself to create a database in my app_data folder and also filled in all the table rows as necessary. Then I moved the code to my sql server running SQL Server 2005 Standard and found out that it won't generate that database in either the app_data folder or the SQL Server Data directory. I've gotten so desperate to get this database created that I've (temporarily) given "Everyone" full permissions to both directories. I then double checked that my named pipes to that server were enabled, and then also tried moving my database from my working sql express app from a different computer to the server. I still cannot get it to work.
If I have no database in the folders that worked on sql express i get this error:
"SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express ..."
If I have a database in the folder it says it can't communicate with it or can't find it.
My web.config shows:
<add name="LocalServer" connectionString="server=SERVERNAMEWest;database=ASPNETDB.MDF;uid=UserName;pwd=Password;" PoviderName="System.Data.SqlClient" />
Is there a way to successfully generate this database with sql server? Please Help!!!
Stored Procedure Does Not Work
Nov 15, 2006Hi All
Please review this vb6 code (the stored procedure was added by aspnet_regsql.exe)
Thanks
Guy
With CMD .CommandText = "aspnet_Users_CreateUser" Call .Parameters.Refresh .Parameters(1).Value = "812cb465-c84b-4ac8-12a9-72c676dd1d65" .Parameters(2).Value = "myuser" .Parameters(3).Value = 0 .Parameters(4).Value = Now() Call RS.Open(CMD) End With
The error I get is :Invalid character value for cast specification.
This is the stored procedure code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[aspnet_Users_CreateUser]
@ApplicationId uniqueidentifier,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
IF( @UserId IS NULL )
SELECT @UserId = NEWID()
ELSE
BEGIN
IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
WHERE @UserId = UserId ) )
RETURN -1
END
INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
RETURN 0
END
Cannot Get Stored Procedure To Work
Dec 10, 2007I am trying to pass a value from one stored procedure into another. I have got the following stored procedures:
Stored Procedure 1:ALTER PROCEDURE test2
(@Business_Name nvarchar(50)
)
AS
BEGINDECLARE @Client_ID INT
SET NOCOUNT ON;
INSERT INTO client_details(Business_Name) VALUES(@Business_Name) SELECT @Client_ID = scope_identity()
IF @@NESTLEVEL = 1
SET NOCOUNT OFF
RETURN @Client_ID
END
Stored Procedure 2 - Taking Client_ID from the proecedure test2ALTER PROCEDURE dbo.test3
(
@AddressLine1 varchar(MAX),
@Client_ID INT OUTPUT
)
ASDECLARE @NewClient_ID INT
EXEC test2 @Client_ID = @NewClient_ID OUTPUT
INSERT INTO client_premises_address(Client_ID, AddressLine1) VALUES(@Client_ID, @AddressLine1)
SELECT @NewClient_ID
When I run this proecedure I get the following error:
FailedProcedure or function 'test2' expects parameter '@Business_Name', which was not supplied. Cannot insert the value NULL into column 'Client_ID', table 'C:INETPUBWWWROOTSWWEBSITEAPP_DATASOUTHWESTSOLUTIONS.MDF.dbo.client_premises_address'; column does not allow nulls. INSERT fails. The statement has been terminated.
However If I run Stored Procedure Test2 on its own it runs fine
Can anyone help with this issue? Is there something that I have done wrong in my stored procedures?
Also does anyone know If I can use the second stored procedure in a second webpage, but get the value that was created by running the stored proecdure in the previous webpage?
This Stored Procedure Does Not Work! Why?
Apr 19, 2004here is code i have
private void btnGetCustomers_Click(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection ();
conn.ConnectionString = " Data Source=(local); Initial Catalog=Northwind; Integrated Security=SSPI ";
SqlCommand cmd = conn.CreateCommand ();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "procCustomers";
// add the input parameters and set their values
cmd.Parameters.Add(new SqlParameter("@ContactName", SqlDbType.VarChar, 50));
cmd.Parameters["@ContactName"].Value = txbxContactName.Text;
conn.Open ();
SqlDataReader dr = cmd.ExecuteReader ();
if (dr.HasRows == true)
{
dgrdMyDataGrid.DataSource = dr;
dgrdMyDataGrid.DataBind ();
}
else
Response.Write("Nothing found.");
// clean up
dr.Close ();
conn.Close ();
}
and here is the stored procedure:
CREATE PROCEDURE procCustomers
@ContactName nvarchar(50)
AS
SELECT * FROM customers
WHERE customers.contactname LIKE @ContactName
ORDER BY customers.contactname ASC
GO
no compile errors, it just returns nothing even if there is supposed to be a match... what am doing wrong?
Stored Procedure Cannot Work In .NET
Jul 30, 2005Hi, i encounter a problem in the .NET. I have
write the stored prodeure for the registering function and it works
perfectly in the SQL Server. But when i called the stored procedure
from the .NET, it cannot work. Can someone pls help? Thanks!
Stored Procedure Codes:
CREATE PROCEDURE spAddProfile(@MemNRIC CHAR(9), @MemName
VARCHAR(30), @MemPwd VARCHAR(15), @MemDOB DATETIME, @MemEmail
VARCHAR(80), @MemContact VARCHAR(8))
AS
IF EXISTS(SELECT MemNRIC FROM DasMember WHERE MemEmail = @MemEmail)
--RETURN -200
IF EXISTS(SELECT MemEmail FROM DasMember WHERE MemNRIC = @MemNRIC)
RETURN -201
IF EXISTS(SELECT DATEDIFF(YEAR, @MemDOB, GETDATE())
FROM DasMember
WHERE DATEDIFF(YEAR, @MemDOB, GETDATE()) < 18)
RETURN -202
INSERT INTO DasMember(MemNRIC, MemName, MemPwd, MemDOB, MemEmail,
MemContact, MemDateJoin) VALUES (@MemNRIC, @MemName, @MemPwd, @MemDOB,
@MemEmail, @MemContact, GETDATE())
IF @@ERROR <> 0
RETURN @@ERROR
SET @MemNRIC = @@IDENTITY
RETURN
DECLARE @status int
EXEC @status = spAddProfile 'S1234567J', 'Kim', 'kim', '1986-01-01', 'kim@hotmail.com', '611616161'
SELECT 'Status' = @status
When called from .NET, it cannot works.. These are the codes:
Private Sub btnRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim connStr As String =
System.Configuration.ConfigurationSettings.AppSettings("SqlConnection.connectionString")
Dim dbConn As New SqlConnection
dbConn.ConnectionString = connStr
Try
dbConn.Open()
Dim dbCmd As New SqlCommand
dbCmd.Connection = dbConn
dbCmd.CommandType = CommandType.StoredProcedure
dbCmd.CommandText = "spAddProfile"
Dim dbParam As SqlParameter
dbParam = dbCmd.Parameters.Add("@return", SqlDbType.Int)
dbParam.Direction = ParameterDirection.ReturnValue
dbParam = dbCmd.Parameters.Add("@MemNRIC", SqlDbType.Char, 9)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = txtNRIC.Text
dbParam = dbCmd.Parameters.Add("@MemName", SqlDbType.VarChar, 30)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = txtName.Text
dbParam = dbCmd.Parameters.Add("@MemPwd", SqlDbType.VarChar, 15)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = txtPwd.Text
dbParam = dbCmd.Parameters.Add("@MemDOB", SqlDbType.DateTime)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = txtDOB.Text
dbParam = dbCmd.Parameters.Add("@MemEmail", SqlDbType.VarChar, 80)
dbParam.Direction = ParameterDirection.Input
dbParam.Value = txtEmail.Text
dbParam = dbCmd.Parameters.Add("@MemContact", SqlDbType.VarChar, 8)
dbParam.Direction = ParameterDirection.Input
dbCmd.ExecuteNonQuery()
Dim status As Integer
status = dbCmd.Parameters("@return").Value
If status = -201 Then
lblError.Text = "NRIC already exists!"
ElseIf status = -202 Then
lblError.Text = "You must be at least 18 years to sign up!"
Else
lblError.Text = "Success"
End If
Catch ex As SqlException
lblError.Text = ex.Message
Catch ex As Exception
lblError.Text = ex.message
End Try
End Sub
End Class
Can someone pls help? Thanks a lot!! I appreciated it very much!!
Stored Procedure Do Not Work
Oct 26, 2000Hi
When I tried to execute a procedure it give me message:
"Too many arguments were supplied for procedure sp2_ge_ag15_01"
I am doing:
exec sp2_ge_ag11_01 306,'NOVO VELHO',Null,
'1968-04-15','M',90,.17,'novo@com.br','teste'
It has 9 parameters
the begin of the my procedure is:
CREATE procedure dbo.sp2_ge_ag11_01(@Pcd_ficha integer,
@Pnm_ficha varchar(60),
@Pcd_pac integer = null,
@Pdt_nas datetime,
@Psx_pac varchar(01),
@Ppes_pac smallint,
@Palt_pac float,
@Pemail1 varchar(50),
@Pobs varchar(254))
AS
etc...
What Happen
thank you in advance
Help! Stored Procedure Does Not Work On Second Server
Sep 8, 1999I have a stored procedure that works on my development server but when I placed it on the server that is to be the prodcuction server i get the following response:
output ----------------
The name specified is not recognized as an internal or external command, operable program or batch file.
This is the procedure:
CREATE PROCEDURE sp_OutputClaim @OutFile varchar(255), @CurAns char(8) AS
declare @CmdLine varchar(255)
select FieldX into ##TempTable from Table where FieldY = @CurAns
select @CmdLine = "exec master..xp_cmdshell 'bcp tempdb.dbo.##TempTable out
c:est" + @OutFile + " /Uxx /Pxxx /Sxx /f c:estcp.fmt'"
exec (@CmdLine)
drop table tempdb..##TempTable
Thanks for any help !!!!
Rob
Stored Procedure Sort Parameter Doesnt Work
Jul 22, 2006Hello, I am trying to make this.
CREATE PROCEDURE [dbo].[P_SEL_ALLPERSONAS]
@nmpersona int,
@sortorder varchar(20)
AS
BEGIN
select nmpersona, dsprimernombre, dssegundonombre,
dsprimerapellido, dssegundoapellido
from personas
order by @sortorder
END
But I got this error. Please help
Msg 1008, Level 16, State 1, Procedure P_SEL_ALLPERSONAS, Line 13
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Does The .. Expression In Stored Procedure Work In SSRS 2000 ?
Mar 13, 2007Hi,
I am not understanding this part of the problem. I am currently reusing a stored procedure that has a ".." as part of the select statement.
I can't put the select statement up here due to privacy but I have found the error where the error states the following:-
Invalid Column Prefix: AM, invalid table name.
I noticed that part of the select statement was the following:-
AM..Field1
I tried executing this stored procedure in the query analyzer and it works fine, but when I tried executing it in SSRS, it gives me the error. After searching through the internet for possible causes, I found out that it was the ".." was giving the error. Anyone knows why ? I found out that it was supposed to bypass any users and permissions to the table.
Thakns !
Bernard
Using Return X In A Stored Procedure Doesn't Work With Table Adapters
Mar 9, 2007Hi,
I was trying to create a simple SP that return a single value as follows:CREATE PROCEDURE IsListingSaved@MemberID INT,@ListingID INTASIF EXISTS (SELECT [Member_ID] FROM [Member_Listing_Link] WHERE [Member_ID] = @MemberID AND [Listing_ID] = @ListingID) Return 1ELSE Return 0GOWhen I try it out in the Tableadapter's preview table, I get the correct result (1, where the entries exist). However, in the BLL, I tried to get the value as:Dim intResult as IntegerintResult = CType(Adapter.IsListingSaved(intMemberID, intListingID), Integer). However, this always returns 0 (when it should be returning 1). P.S. Curiously, breakpoints skipped the VS generated code for the adapter. What could be the problem? Thanks,Wild Thing
SQL Server Insert Update Stored Procedure - Does Not Work The Same Way From Code Behind
Mar 13, 2007All:
I have created a stored procedure on SQL server that does an Insert else Update to a table. The SP starts be doing "IF NOT EXISTS" check at the top to determine if it should be an insert or an update.
When i run the stored procedure directly on SQL server (Query Analyzer) it works fine. It updates when I pass in an existing ID#, and does an insert when I pass in a NULL to the ID#.
When i run the exact same logic from my aspx.vb code it keeps inserting the data everytime! I have debugged the code several times and all the parameters are getting passed in as they should be? Can anyone help, or have any ideas what could be happening?
Here is the basic shell of my SP:
CREATE PROCEDURE [dbo].[spHeader_InsertUpdate]
@FID int = null OUTPUT,@FLD1 varchar(50),@FLD2 smalldatetime,@FLD3 smalldatetime,@FLD4 smalldatetime
AS
Declare @rtncode int
IF NOT EXISTS(select * from HeaderTable where FormID=@FID)
Begin begin transaction
--Insert record Insert into HeaderTable (FLD1, FLD2, FLD3, FLD4) Values (@FLD1, @FLD2, @FLD3,@FLD4) SET @FID = SCOPE_IDENTITY(); --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 1 return @rtncode end endELSE
Begin begin transaction
--Update record Update HeaderTable SET FLD2=@FLD2, FLD3=@FLD3, FLD4=@FLD4 where FormID=@FID;
--Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 2 return @rtncode end
End---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Thanks,
Blue.
SQL 2012 :: SSIS - Execute Stored Procedure With Parameters Does Not Work
Jun 9, 2015Using the following:
SQL Server: SQL Server 2012
Visual Studio 2012
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
When I use the following execute statement where I am "Hard Coding" in the parameters, the stored procedure runs successfully and it places the data into the table per the stored procedure.
SQLStatement: dbo.sp_ml_location_load @system_cd='03', @location_type_cd=Store;
However, the @system_cd parameter can change, so I wanted to set these parameters up as variables and use the parameter mapping in the Execute SQL Task.
I have set this up as follows and it runs the package successfully but it does not put the data into the table. The only thing I can figure is either I have the variables set up incorrectly or the parameter mapping set up incorrectly.
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................
Here is my set up, what is wrong here:
I Created these Variables:
Name Scope Data Type Value
system_cd Locations String '03'
location_type_cd Locations String Store
I added these parameter mappings in the Execute SQL Task
Variable Name Direction Data TypeParameter NameParameter Size
User::system_cd Input NVARCHAR@system_cd -1
User::location_type_cd Input NVARCHAR@location_type_cd -1
I used this SQLStatement: EXEC dbo.sp_ml_location_load ?,
It runs the package successfully but it does not put the data into the table.
Temporary Table In Stored Procedure Doesn't Work From SQLDataSource
Feb 20, 2008I have a stored procedure with the following:
CREATE TABLE #t1 (... ...);
WITH temp AS (....)
INSERT INTO #t1
SELECT .... FROM temp LEFT OUTER JOIN anothertable ON ...
This stored procedure works fine in Management Studio.
I then use this stored procedure in an ASP.NET page via a SQLDataSource object. The ASP.NET code compiles without error, but the result returned is empty (my bounded GridView object has zero rows). From Web Developer, if I try to Refresh Schema on the SQLDATASource object, I get an error: "Invalid object name '#t1'. The error is at the red #1 as I tried putting something else at that location to confirm it.
What does the error message mean and what have I done wrong?
Thanks.
The Multi Delete && Multi Update - Stored Procedure Not Work Ok
Feb 4, 2008the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
Where Do The Migrated Files Go?
Jun 7, 2007hi all,
Where do the migrated files go?
After you run the wizard where ARE the DTS packages?
the status for migrated is success, and in the log file show:
#Message=Successfully saved the package "DTSMeasurements" in the destination "A0001SQLSERVER2005DTSMeasurements".
but i cant find.
Please help.
thanks a lot
Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Migrated Ssis Package
May 11, 2007Hi All,
we have migrated dts 2000 packages to ssis packages.
but iam unable to see the migrated ssis package in msdb under integration services.
iam getting error;
failed to retrieve data for this request. (Microsoft.sqlserver.smoenum)
additional information:
login timeout expired
an error has occured while establishing a connection to the server. when connecting to sql server 2005, this failure may be caused by the fact that under the dafault settings sql server does not allow remote connections.
named pipes provider : could not open a connection to sql server [2]. (Microsoft sql native client]
please give your suggestions asap .
DTS Migration Wizard, Where Does It Put Migrated Version?
Oct 30, 2006From SQL Server Management Studio I right click on Management/Legacy/DTS and select Migration Wizard and enter a SQL Server Source and Destination. A list of DTS packages from the source server is displayed and I select one of the DTS packages and give the destination a unique name. The wizard runs and says it's successful. I can't find the new SSIS version of the DTS package with the new name that I've given it, however. Where does it put the new migrated copy of the DTS package.
Thanks,
John
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Mar 3, 2008Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
2Gb Access Database Became Nearly 4Gb Migrated To SQL Server Express - Why?
Jan 8, 2007I have just installed SQL Server Express 2005 to get around the 2Gb database size limits under Access 2003. My simple 2Gb Access database (simple meaning only 10 tables with 2-10 fields each, vast majority of the data in one of them and mainly Long Integers) became nearly 4Gb when migrated using SSMA - not great when the SQL Server Express database limit is 4Gb. Shrinking makes no difference.
Can anyone suggest why in general a SQL Server Express database would be nearly twice the size as its Access equivalent. Are there some useful tricks or techniques to reduce the apparently massive overhead?
Can I Use Db Mail Component Of SSIS On Migrated DTS Package ?
Dec 10, 2007Hi
I had a DTS package on sql2000 which i migrated succesfully to Sql2005 and im able to open the package and execute the package.Now i want to add a new database mail component on this package to send emails to recepients.In short i dont want to use SQL Mail component of Sql2000 which required outlook components,instead i want to use the new features of SSIS to my package which was designed on sql2000.
Is it possible to use the SSIS new features to be incorporated on my old DTS package?
Thanks in advance
Regards
Arvind
Trying To Find Code For SSIS Package That Has Been Migrated
Mar 24, 2008We have a sql job type SSIS package that is not populating a database table as expected and I'm trying to figure what why not but I'm not sure where the "code" is. The person that created it said they copied a legacy package (sql 2000) and made modifications to it, then migrated it to the new version. When a legacy package is migrated is there a SSIS solution built? I see you can edit SSIS packages only in BI Development Studio but I'm not sure where to find package.
This post may answer my question but if you have any additional or updated information I'd like to hear it.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=393634&SiteID=1
So, to view or edit the package you have to export it?
Package location
Select the type of storage location to export the package to. The following options are available:
SQL Server
File System
SSIS Package Storage
So do you pick "File System" in order to view it in BI?
Thanks for your help.
Migrated SSRS With Old Server Name In Subscription Link
Jan 16, 2008
Hi there,
Some months ago, we migrated our SQL Server Reporting services server from Server 'Alpha' to server 'Bravo'. I thought we performed all of the steps needed to accomplish this task based on various Microsoft Articles that we identified. The fact that everything has been running fine (we thought) for the last several months confirmed our belief that everything was done.
Our problem found the other day is with a report 'Subscription'. The other day, a user called me and said their subscription is coming to them fine with the report displayed, but the link to re-run the report at the bottom of the e-mailed document doesn't work. I looked at the e-mail they received and found that the link had 'http://alpha/reportserver/...' instead of 'http://bravo/reportsserver/...'. Since 'Bravo' is the name of the new server, I new something was a miss. I tried creating a new subscription to a report and the result was the same 'http://alpha/reportserver/...' I've looked high and low in the SQL Reporting services configuration and can not figure out where I missed a change from 'Alpha' to 'Bravo'.
Has anyone seen anthing like this? If you can help me out it would be greatly appreciated. Thanks! - Eric-
SSIS Is Truncating Data Being Migrated From Mainframe
Feb 13, 2007I have data on the mainframe that contains special characters such as a copyright symbol (letter c with a circle) when running the SSIS package migration from mainframe database to SQL Server 2005 database the data in the column is truncated at the point it hits this symbol with no errors. I had error reports set up for truncation and did not see an error report for this.
I have tried to change the data type to different data types and re-run nothing changes things.
The symbol is a EBCDIC 'b4' on the mainframe side - I have no idea what that would translate to.
Anyone have any ideas? Am I stuck with substituting something else for the symbol on the mainframe side and re-running the package? Is there anyway to handle this situation in SSIS? I am using the Import/Export Wizard for this one because before I ran into this problem it seemed to be working fine.
Thanks, MaryOS
T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure
Nov 14, 2014I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?
Jan 29, 2015I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.
Sep 19, 2006I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page
Dec 28, 2005I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedSQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?
Sep 26, 2014I 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
How To Change The Target Table Name In SSIS Package(not Designed From Scratch But Was Migrated)
Nov 6, 2007In Past, I created DTS package on 2000 version, that import TXT file into SQL 2000 table.
Now I migrated the DTS to DTSX (SSIS) package, and all is working fine. but I can not find how can I edit or modify the target table name in DTSX(SSIS) package in BI Studio.
can you some body help.
Thanks
Why Won't My Sql Procedure Work?
Apr 13, 2005Hi
For some reason my stored procedure is returning no results, when I
know it should be. I am trying to take a textbox someone has
typed into and returning all names from the database which include that
string. My SQL statement is
ALTER PROCEDURE getLecturerNames
(
@lName as varchar(20)
)
AS
SELECT * FROM Lecturers
WHERE lName LIKE '%@lName%'
When I step into the procedure, the @lName parameter shows lName =
'ike' (what I typed to search) but no results are returned.
However when I change the procedures last line to :
WHERE lName LIKE '%ike%'
2 records are returned. Is SQL server adding the quotation marks causing the problem, or if not what is?
System Stored Procedure Call From Within My Database Stored Procedure
Mar 28, 2007I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance