I have a SQL Server 2005 server with no maintenance plans configured and no SQL agent jobs. The SQL logs are indicating that every night around midnight the databases I have created are all taken offline and backed up. How do I determine what is triggering these backups?
View 1 RepliesHi,
we're accessing a SQL Server as a source for some SSIS packages using quite complex SQL commands. We have dataflows getting data from up to 10 queries. The problem is that SSIS starts all these queries in parallel using up all the memory of the server (the source SQL server, not the server SSIS is running on). So the queries are very slow. Is there any way to force SSIS to start the queries after each other?
I already browsed the web for some answers on that and I'm not very optimistic... Maybe the only solution is really to feed the result of the query in raw files and process them later...
Thanks,
I am attempting to pull in data from a flat file data source that contains dates in the following format "01012007 10:22" which translates to Month Day Year and Military Time. I want to turn this into a DateTime format so that I can insert it into the proper column. I have a SQL statement which will do this (see bellow), but I can't figgure out how to run the statement on the data before it reaches its destination.
Can anyone help?
The code is:
Code Block
cast(convert(varchar(16),(substring( REPORT_RUN_DATE,1,2 ) + '/' + substring( REPORT_RUN_DATE,3,2 ) + '/' + substring( REPORT_RUN_DATE,5,10 )),1) as datetime) AS REPORT_RUN_DATE
Does any one know what could be making the size of a backup get so big in SQL server?
I noticed if you back-up the same database in Enterprise Manager over and over (without making any changes to the database), the size of the backup gets bigger and bigger. To get around this I simply erase the backup and create a new one.
Now I'm experiencing the same kind of problem, different situation. I decided to make very few changes to my database. If anything, I shrunk the size of the tables and stored procedures.... Now all of a sudden my database backup is 7 times larger.
What could be increasing the size so much, if I haven't increased the amount of tables or stored procedures?
What is the log file about? Mine is huge? Is there a way to reset it or clear it?
any help would be great.
Thank you,
Alec
Hello everyone.
First off, I know little to nothing about SQL. I run the IIS web server and the programmers run the SQL server. Recently the programmers found a backup running everynight at midnight and its messing up the Differential backups. By that I mean, since the full backup runs, the differential isnt backing anything up since it is set to only backup from the last full backup.
The backup seems to be backing up to a "Virtual Device" and I have no idea what that is. I am unable to see any user associated with the backup and I'm at a total loss as to where the data is going. Can someone please help me resolve this issue. I searched but was unable to find anything as I have no idea what to search for to find where this backup is coming from.
Here is a log from said backup...
Database backed up. Database: dbname, creation date(time): 2008/03/10(03:40:58), pages dumped: 1, first LSN: 288:523:56, last LSN: 288:547:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{B9A0EFAB-8894-4BBC-BC77-82DA1B58E2D4}28'}). This is an informational message only. No user action is required.
Lastly... The programmers do have backups running.. full once a week and differentials inbetween. the problem is, those logs look like this...
Log was backed up. Database: dbname, creation date(time): 2008/04/09(03:32:53), first LSN: 135:305:1, last LSN: 135:305:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:ackup imeanddate.BAK'}). This is an informational message only. No user action is required.
Thank you in advance!
Hi,
I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).
When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:
STRTOMEMBER(@DimOrganisationBUSADDRSTATE, CONSTRAINED).
When running the report and selecting the 'Unkown' value from the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.
How can I prevent the constrained flag from being used, or am I doing something wrong with converting null values to 'Unknown'?
Thanks
This is what I want to do:1. Delete all tables in database with table names that ends with anumber.2. Leave all other tables in tact.3. Table names are unknown.4. Numbers attached to table names are unknown.5. Unknown number of tables in database.For example:(Tables in database)AccountAccount1Account2BinderBinder1Binder2Binder3.......I want to delete all the tables in the database with the exceptionof Account and Binder.I know that there are no wildcards in the "Drop Table tablename"syntax. Does anyone have any suggestions on how to write this sqlstatement?Note: I am executing this statement in MS Access with the"DoCmd.RunSQL sql_statement" command.Thanks for any help!
View 2 Replies View RelatedI'm building a simple webform, except Visual Studio and my service provider have combined to drive me nutty.
First, I MUST use an ODBC connection to my remote SQL Server do to some unknown configuartion problem. I've been playing with Visual Studio for only a month, so normally when something goes wrong I can go look in the mirror and find the culprit. This is different. I've got a totally functional web form with a SQL Connection, but when I try to change it to an ODBC Connection, I get the following error.
An OdbcParameter with ParameterName '@CertHolder' is not contained by this OdbcParameterCollection
My coding is fine because I stole it straight from the walkthrough and it works. But the specifications that Visual Studio provide are quite suspect. Please note the failure to include some key "@" signs.
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.cmdUpdate = New System.Data.SqlClient.SqlCommand
Me.cmdGetAll = New System.Data.SqlClient.SqlCommand
Me.cmdSelect = New System.Data.SqlClient.SqlCommand
Me.OdbcConnection1 = New System.Data.Odbc.OdbcConnection
Me.OdbcGetAll = New System.Data.Odbc.OdbcCommand
Me.OdbcSelect = New System.Data.Odbc.OdbcCommand
Me.OdbcUpdate = New System.Data.Odbc.OdbcCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "this works fine"
'
'cmdUpdate
'
Me.cmdUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _
"City = @City, State = @State, Zip = @Zip, CertHolder = WHERE (CertHolder = @Cert" & _
"Holder)"
Me.cmdUpdate.Connection = Me.SqlConnection1
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50, "Name"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 50, "Address"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address2", System.Data.SqlDbType.NVarChar, 50, "Address2"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50, "City"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", System.Data.SqlDbType.NVarChar, 50, "State"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Zip", System.Data.SqlDbType.NVarChar, 50, "Zip"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
'
'cmdGetAll
'
Me.cmdGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData"
Me.cmdGetAll.Connection = Me.SqlConnection1
'
'cmdSelect
'
Me.cmdSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _
"HERE (CertHolder = @CertHolder)"
Me.cmdSelect.Connection = Me.SqlConnection1
Me.cmdSelect.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, "CertHolder"))
'
'OdbcConnection1
'
Me.OdbcConnection1.ConnectionString = "This works fine"
'
'OdbcGetAll
'
Me.OdbcGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData"
Me.OdbcGetAll.Connection = Me.OdbcConnection1
'
'OdbcSelect
'
Me.OdbcSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _
"HERE CertHolder = @CertHolder"
Me.OdbcSelect.Connection = Me.OdbcConnection1
Me.OdbcSelect.Parameters.Add(New System.Data.Odbc.OdbcParameter("CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, "CertHolder"))
'
'OdbcUpdate
'
Me.OdbcUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _
"City = @City, State = @State, Zip = @Zip WHERE CertHolder = @CertHolder"
Me.OdbcUpdate.Connection = Me.OdbcConnection1
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Name", System.Data.Odbc.OdbcType.NVarChar, 50, "Name"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address", System.Data.Odbc.OdbcType.NVarChar, 50, "Address"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address2", System.Data.Odbc.OdbcType.NVarChar, 50, "Address2"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("City", System.Data.Odbc.OdbcType.NVarChar, 50, "City"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("State", System.Data.Odbc.OdbcType.NVarChar, 50, "State"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Zip", System.Data.Odbc.OdbcType.NVarChar, 50, "Zip"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Original_CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
I NEVER EVER TYPED ORIGINAL_CERTHOLDER IN THE SQL PREPARATION
End Sub
Protected WithEvents btnSave As System.Web.UI.WebControls.Button
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents ddlCertHolder As System.Web.UI.WebControls.DropDownList
Protected WithEvents txtName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox
Protected WithEvents ddlCH As System.Web.UI.WebControls.DropDownList
Protected WithEvents cmdUpdate As System.Data.SqlClient.SqlCommand
Protected WithEvents cmdGetAll As System.Data.SqlClient.SqlCommand
Protected WithEvents cmdSelect As System.Data.SqlClient.SqlCommand
Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox
Protected WithEvents txtState As System.Web.UI.WebControls.TextBox
Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox
Protected WithEvents OdbcConnection1 As System.Data.Odbc.OdbcConnection
Protected WithEvents OdbcGetAll As System.Data.Odbc.OdbcCommand
Protected WithEvents OdbcSelect As System.Data.Odbc.OdbcCommand
Protected WithEvents OdbcUpdate As System.Data.Odbc.OdbcCommand
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Comments? Suggestions, I am not positive about how to fix this.
I'm using 2014 SE.I know a backup of a database doesn't take the source DB offline, but then I need to move this DB to another server (for intensive reporting work). At present we restore the DB, but that means putting the DB in single user mode, kicking everyone off, and completing the restore.
I see from 2014 EE notes that "online restore" is possible. EE is of course, mightily expensive.Or perhaps it's possible to configure things to speed up the restore process somehow, so there is less downtime?, the resource impact in creating the backup is quite high, perhaps there's a way to (apart from playing with backup compression) reduce the impact on the source server here?
I am trying to create sql code that restores a backup of a master database to a new database on the same server. It “seems” to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!
Any and all help is sincerely appreciated.
The master databases from which the backups are made start with “MODTRNMaster”
The databases which are created from the restores start with “M1_” and “M2_”. (We call them training room databases.)
My script for backing up the master databases:
-- Backup the master training database
backup database MODTRNMaster
to disk = 'f:kupMODTRNMaster.bak'
backup database MODTRNMaster_IMG
to disk = 'f:kupMODTRNMaster_IMG.bak'
backup database MODTRNMaster_MNC
to disk = 'f:kupMODTRNMaster_MNC.bak'
backup database MODTRNMaster_VM
to disk = 'f:kupMODTRNMaster_VM.bak'
go
This is the restore script for restoring the first training room databases. I’m hoping that there is just something simple that I’m overlooking in these restore statements! J
-- Restore the backup of the master training database into the
-- training room #1 database.
use master
go
drop database M1_MSLH
go
restore database M1_MSLH
from disk = 'f:kupMODTRNMaster.bak'
with move 'DEV5_Data' to 'f:mssqldataM1_MLSH.mdf',
move 'MM' to 'f:mssqldataM1_MLSH_1.mdf',
move 'AMB' to 'f:mssqldataM1_MLSH_2.mdf',
move 'DM' to 'f:mssqldataM1_MLSH_3.mdf',
move 'IMM' to 'f:mssqldataM1_MLSH_4.mdf',
move 'ED' to 'f:mssqldataM1_MLSH_5.mdf',
move 'DEV5_Log' to 'f:mssqllogM1_MLSH_log.ldf',
recovery
go
Thanks in advance
:eek:
:eek:
Hello,
I need to perform an update to one of my columns in my gridView. Is it possible to pass in the "column name" during runtime as a parameter to a stored procedure. I tried doing that but it doesn't seem to work? I might be doing something wrong of course.
Can anyone give me some advice on how to do this?
I'm sure that the try part of following code are all executedand the session("isLogin") has set to Truebut it always catch a exceptionand redirect to error1.aspx can't figure it out 1 Try
2 mySqlCon = New SqlConnection(strMySqlCon)
3 mySqlCmd = New SqlCommand(strMySqlCmd, mySqlCon)
4
5 mySqlCon.Open()
6 myDataReader = mySqlCmd.ExecuteReader()
7
8 If myDataReader.Read() = True Then
9
10
11 webPwdMd5 = System.Web.Security.FormsAuthentication. _
12 HashPasswordForStoringInConfigFile(Me.TextBox1.Text, "MD5")
13
14 If webPwdMd5 = myDataReader.Item("password") Then
15 Session("isLogin") = True
16 'Me.TextBox1.Text = "ppp"
17 Response.Redirect("main.aspx")
18 Else
19 Session("islogin") = False
20 Me.Label1.Visible = True
21 End If
22 Else
23 Session("isLogin") = False
24 Me.Label1.Visible = True
25 End If
26
27 mySqlCon.Close()
28
29 Catch Myexception As Exception
30 Session("isLogin") = False
31 Response.Redirect("error1.aspx")
32
33 Finally
34
35 End Try
The Microsoft SQL Server icon is showing on my task bar but with a white (blank) circle "stamped" over the bottom right of the icon.
To me it seems that the implication is that somehow the server is not being recognized as (local)etSDK.
Resting the mouse arrow on the Server icon displays: unknown - \ - MSSQLServer
Needless to say something is quite wrong and I cannot open nor create ANY DATA while on WebMatrix.
If I right-click on the server icon and select "Start" I get the following error: "Invalid handle" which apparently is error number 6.
Previously, after many try-outs I managed to at least put the server icon on the task bar by making use of a setup.ini file that reads as shown below (and then typing from the MSDE sub directory while in MSDOS "setup.exe").
[Options]
INSTANCENAME=CADComputing
SECURITYMODE=SQL
SAPWD=myPassword539
Is there a way out of this mess?????
Hello!!!
I have installed sql 7.0 with sp1. when I check the files I saw to more file extra i.e. distmdl.mdf and distmdl.ldf along with system and pubs and northwind database files.
Can any one tell me what are these(distmdl.mdf and distmdl.ldf ) files.
Thank you!!!!!
Dindu
protected void Button4_Click(object sender, EventArgs e)
{
string selectedItem = DropDownList1.SelectedItem.Text;
if (selectedItem == "zezo")
{
string connectionString2 = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection connection2 = new SqlConnection(connectionString2);
connection2.Open();
SqlCommand command2 = new SqlCommand("finde", connection2);
command2.CommandType = CommandType.StoredProcedure;
command2.Parameters.Add("@columen_name", SqlDbType.NVarChar).Value = "taher";
command2.ExecuteNonQuery();
}
and this is the stored procedure
ALTER PROCEDURE dbo.finde @columen_name nvarchar(50)
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
declare
@maxcount int,@sql varchar(8000)
set @maxcount=(select max(taher)+1 from counter)
set @sql='insert into counter (' + @columen_name +')
values ( @maxcount )'
exec (@sql)
************************
and this is the error
Server Error in '/NTSOLUTIONS' Application.
--------------------------------------------------------------------------------
Must declare the scalar variable "@maxcount".
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@maxcount".
Source Error:
*********************************************
so i can not find where is the exact point
zezo
I'm running into a situation that has me adding a value of "Unknown" toa reference table. I am being pulled between two trains of thought, andwas curious to get other's input on in. I give an example below.1-) Adding "Unknown" to a reference table is bad. Doing so effectivelychanges the Nullability option of every FK that references the table toa NULLable FK relation.2-) Simply adding a "Not Known/Undetermined" value to the referencetable greatly simplifies things. No schema changes are required, andprograms that use the reference table to populate their drop-downs willautomatically see the new value.Perhaps both approaches are good, but it would all depend of thecontext, the criticality of other FKs that reference the table, how/whenthe data is being used?==============================================EXAMPLE==============================================Assume two tables. Employee & EyeColor, as described below.+===================================+|Employee |+----------------+------------------+|EmployeeId(PK) | EyeColorId (FK) ||NOT NULL | NOT NULL |+----------------+------------------+|marc | 1 ||dan | 2 ||sonya | 1 |+================+==================++================================================= ===+|EyeColor |+---------------+------------------+-----------------+|EyeColorId(PK) | EnglishName(AK1) | FrenchName(AK2) ||NOT NULL | NOT NULL | NOT NULL |+---------------+------------------+-----------------+| 1 | Brown | Brun || 2 | Bloodshot | Rouge || 3 | Blue | Bleue |+===============+==================+============== ===+And let's say that an automated process is being built to import EyeColors from central database. In this process EyeColor may no longer beavailable.With solution #1, new (or existing) data is changed as follows:+----------------+------------------+|EmployeeId(PK) | EyeColorId (FK) ||NOT NULL | NULL |+----------------+------------------+|marc | 1 ||dan | 2 ||sonya | 1 ||newemp | NULL |+================+==================++================================================= ===+|EyeColor |+---------------+------------------+-----------------+|EyeColorId(PK) | EnglishName(AK1) | FrenchName(AK2) ||NOT NULL | NOT NULL | NOT NULL |+---------------+------------------+-----------------+| 1 | Brown | Brun || 2 | Bloodshot | Rouge || 3 | Blue | Bleue |+===============+==================+============== ===+With solution #2, new (or existing) data is changed as follows:+----------------+------------------+|EmployeeId(PK) | EyeColorId (FK) ||NOT NULL | NULL |+----------------+------------------+|marc | 1 ||dan | 2 ||sonya | 1 ||newemp | 0 |+================+==================++================================================= ===+|EyeColor |+---------------+------------------+-----------------+|EyeColorId(PK) | EnglishName(AK1) | FrenchName(AK2) ||NOT NULL | NOT NULL | NOT NULL |+---------------+------------------+-----------------+| 0 | Unknown | Inconnu || 1 | Brown | Brun || 2 | Bloodshot | Rouge || 3 | Blue | Bleue |+===============+==================+============== ===+*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View Relatedwhy in a adomd connection these items are unknown
AdomdConnection,
connection,
MiningService,
and in the amo connection these itmes are unknown:
server.Connect,
MiningStructure,
miningmodel,
MiningModelAlgorithms
When I try to run my package from SQL Server Agent I get the following errormessage:
Message
Executed as user: NT AUTHORITYNETWORK SERVICE. The return value was unknown. The process exit code was -532459699. The step failed.
It has nothing to do with security in any way: The package actually succeeds!! (no onErrorEvent is thrown, no Error or whatsoever is logged). The only logging I get is this:
#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
PackageStart,KMAUSQL01SRV,NT AUTHORITYNETWORK SERVICE,Test,{3790FAE9-8300-4374-B2B7-6A630A508ED9},{E15D1815-1123-42D5-9D32-75B0237660A3},23.08.2006 17:18:02,23.08.2006 17:18:02,0,0x,Beginning of package execution.
PackageEnd,KMAUSQL01SRV,NT AUTHORITYNETWORK SERVICE,Test,{3790FAE9-8300-4374-B2B7-6A630A508ED9},{E15D1815-1123-42D5-9D32-75B0237660A3},23.08.2006 17:18:24,23.08.2006 17:18:24,0,0x,End of package execution.
PackageStart -> PackageEnd .... And in Fact the package DOES exactly do what it should! Just that it throws this error anyway!
What can I do to even trace down where the error occurs?? Not to mention fix it?
Hello All!!!
I have a 1.1 asp.net applications that has been in production for a little over a year. It is remoted from a webserver --> application server --> that access data server. I am using win2003 servers and sql 2000. Recently, the application has been having an issue retrieving and storing documents to the sql server. When diagnosing the problem I have ran communication testing to make sure the servers are communicating as they should, I have ran the stored procedure to test for retrieval times and/or failure. So far everything has checked out. While researching the issue, I ran across information that suggested changing from the SQLClient provider to the OLEDB provider. All the changes I have tried appear to have no affect.
I will mention that this does seem to occur only when retrieving larger files(probably a communication issue which I have my communications staff looking into). Has anyone experienced this issue and maybe know of any possible solutions?
Thanks in advance
While running an sp_who2, I noticed a status of 'SPINLOOP'. I've never seen this before. Normally I see sleeping, RUNNABLE, and sometimes ROLLBACK.
Has anyone seen this before and/or have any idea what it means.
Sidney Ives
Database Administrator
Sentara Healthcare
With this algorithm you can sum up an unkown number of records, so that an aggregation matches a fixed value.
If there is not an exakt match available, the algorithm returns the nearest possible value!-- Initialize the search parameter
DECLARE@WantedValue INT
SET@WantedValue = 349
-- Stage the source data
DECLARE@Data TABLE
(
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MaxItems INT,
CurrentItems INT DEFAULT 0,
FaceValue INT,
BestUnder INT DEFAULT 0,
BestOver INT DEFAULT 1
)
-- Aggregate the source data
INSERT@Data
(
MaxItems,
FaceValue
)
SELECTCOUNT(*),
Qty
FROM(
SELECT 899 AS Qty UNION ALL
SELECT 100 UNION ALL
SELECT 95 UNION ALL
SELECT 50 UNION ALL
SELECT 55 UNION ALL
SELECT 40 UNION ALL
SELECT 5 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 250 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 90 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 350 UNION ALL
SELECT 450 UNION ALL
SELECT 450 UNION ALL
SELECT 100 UNION ALL
SELECT 100 UNION ALL
SELECT 50 UNION ALL
SELECT 50 UNION ALL
SELECT 50 UNION ALL
SELECT 1 UNION ALL
SELECT 10 UNION ALL
SELECT 1
) AS d
GROUP BYQty
ORDER BYQty DESC
-- Declare some control variables
DECLARE@CurrentSum INT,
@BestUnder INT,
@BestOver INT,
@RecID INT
-- If productsum is less than or equal to the wanted sum, select all items!
IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) <= @WantedValue
BEGIN
SELECTMaxItems AS Items,
FaceValue
FROM@Data
RETURN
END
-- Delete all unworkable FaceValues
DELETE
FROM@Data
WHEREFaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)
-- Update MaxItems to a proper value
UPDATE@Data
SETMaxItems =CASE
WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue
ELSE MaxItems
END
-- Update BestOver to a proper value
UPDATE@Data
SETBestOver = MaxItems
-- Initialize the control mechanism
SELECT@RecID = MIN(RecID),
@BestUnder = 0,
@BestOver = SUM(BestOver * FaceValue)
FROM@Data
-- Do the loop!
WHILE @RecID IS NOT NULL
BEGIN
-- Reset all "bits" not incremented
UPDATE@Data
SETCurrentItems = 0
WHERERecID < @RecID
-- Increment the current "bit"
UPDATE@Data
SETCurrentItems = CurrentItems + 1
WHERERecID = @RecID
-- Get the current sum
SELECT@CurrentSum = SUM(CurrentItems * FaceValue)
FROM@Data
WHERECurrentItems > 0
-- Stop here if the current sum is equal to the sum we want
IF @CurrentSum = @WantedValue
BREAK
ELSE
-- Update the current BestUnder if previous BestUnder is less
IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue
BEGIN
UPDATE@Data
SETBestUnder = CurrentItems
SET@BestUnder = @CurrentSum
END
ELSE
-- Update the current BestOver if previous BestOver is more
IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver
BEGIN
UPDATE@Data
SETBestOver = CurrentItems
SET@BestOver = @CurrentSum
END
-- Find the next proper "bit" to increment
SELECT@RecID = MIN(RecID)
FROM@Data
WHERECurrentItems < MaxItems
END
-- Now we have to investigate which type of sum to return
IF @RecID IS NULL
IF @WantedValue - @BestUnder < @BestOver - @WantedValue
-- If BestUnder is closer to the sum we want, choose that
SELECTBestUnder AS Items,
FaceValue
FROM@Data
WHEREBestUnder > 0
ELSE
-- If BestOver is closer to the sum we want, choose that
SELECTBestOver AS Items,
FaceValue
FROM@Data
WHEREBestOver > 0
ELSE
-- We have an exact match
SELECTCurrentItems AS Items,
FaceValue
FROM@Data
WHERECurrentItems > 0With references to
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73540
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78015
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505
Peter Larsson
Helsingborg, Sweden
Hi all,I have the below user-defined function on mssql 2000 and I can't workout why i'm getting the following error:-----Server: Msg 156, Level 15, State 1, ProcedurefnCalculateOutworkerPaymentForBox, Line 15Incorrect syntax near the keyword 'IF'.Server: Msg 170, Level 15, State 1, ProcedurefnCalculateOutworkerPaymentForBox, Line 23Line 23: Incorrect syntax near ')'.----------CREATE FUNCTION fnCalculateOutworkerPaymentForBox(@boxid int)RETURNS moneyASBEGINRETURN (/* if the box is a paperback */IF (SELECT COUNT(BoxID) AS NoOfBoxes FROM OutworkerBoxes WHERE BoxID= @boxid AND BoxCode LIKE '%PAPER%') > 1/* If the books are paperback, charge 15p each and add on 30p for adescription book to make 45p */SELECT ((endref - StartRef) * 0.15) + (NoOfDescriptionBooks * 0.30)FROM OutworkerBoxes WHERE BoxID = @boxidELSE/* If the books are normal, charge 25p each and add 20p on fordescription books to make 45p */SELECT ((endref - StartRef) * 0.25) + (NoOfDescriptionBooks * 0.20)FROM OutworkerBoxes WHERE BoxID = @boxid)END-----Below is the sql for the table it works with:-----CREATE TABLE [OutworkerBoxes] ([BoxID] [int] IDENTITY (1, 1) NOT NULL ,[OutworkerID] [int] NOT NULL ,[ImportedBy] [int] NULL ,[StartRef] [int] NOT NULL ,[endref] [int] NOT NULL ,[DateIssued] [datetime] NOT NULL ,[BoxCode] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[DealerID] [int] NULL ,[StatusID] [int] NOT NULL ,[IssuedBy] [int] NOT NULL ,[BoxNotes] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,[DateImported] [datetime] NULL ,[NoOfDescriptionBooks] [int] NOT NULL CONSTRAINT[DF_OutworkerBoxes_NoOfDescriptionBooks] DEFAULT (0),CONSTRAINT [PK_OutworkerBoxes] PRIMARY KEY CLUSTERED([BoxID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GO-----If anyone can advise me i'd be most grateful.Thanx in advanceJames
View 2 Replies View RelatedI have a client using SQL 2k, SP2 (due application requirements, SP3 is notan option - the application vendor will not specify why). We are receiving:[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL ServerConnection BrokenThere doesn't appear to be any rhyme or reason as to why this is happening.And...it shows it's ugly head at random places during execution. Has anyoneexperienced this also, or have any ideas on what to look for? I have seennumerous suggestions stating to upgrade from MDAC 2.6 to 2.7. At thispoint, I'm not sure if thats an option based on the vendors application(which by the way they no longer support!).Any ideas will be greatly appreciated.Greg
View 1 Replies View RelatedI don€™t know if this is the case but I need some help. We have itanium server with Microsoft SQL Server 2005 - 9.00.3054.00 (Intel IA-64) Mar 23 2007 18:42:19 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2).
We has had lot of problem with that the sqlserver.exe process hangs 100% cpu. It€™s totally non responsive I€™m not even able to contact the server with DAC. The general solution has been just kill the process. Not nice.
https://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=440746&SiteID=17
The log is full of those
Date,Source,Severity,Message
08/16/2007 09:00:38,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
08/16/2007 08:55:17,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
08/15/2007 13:06:09,spid4s,Unknown,LazyWriter: warning<c/> no free buffers found.
08/14/2007 10:07:08,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
08/09/2007 11:07:29,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
08/06/2007 09:41:13,spid2s,Unknown,LazyWriter: warning<c/> no free buffers found.
08/06/2007 09:14:50,spid2s,Unknown,LazyWriter: warning<c/> no free buffers found.
08/06/2007 08:54:38,spid2s,Unknown,LazyWriter: warning<c/> no free buffers found.
08/06/2007 08:24:59,spid2s,Unknown,LazyWriter: warning<c/> no free buffers found.
08/03/2007 11:09:54,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
08/01/2007 12:30:56,spid2s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/31/2007 10:16:45,spid2s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/30/2007 10:44:22,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/24/2007 01:03:36,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/23/2007 01:02:33,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/16/2007 12:27:52,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/16/2007 12:25:12,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/16/2007 11:54:07,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/16/2007 11:44:16,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/14/2007 01:04:15,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/12/2007 06:18:51,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/12/2007 06:06:49,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/10/2007 13:20:08,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/10/2007 13:10:43,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/09/2007 10:36:27,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/09/2007 10:28:46,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/06/2007 10:12:20,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/06/2007 10:09:39,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/06/2007 10:06:19,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/06/2007 10:01:28,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/06/2007 01:17:08,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/06/2007 01:13:47,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/04/2007 10:41:06,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/03/2007 10:24:24,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
07/03/2007 01:22:44,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
06/28/2007 01:03:03,spid3s,Unknown,LazyWriter: warning<c/> no free buffers found.
LazyWriter: warning, no free buffers found.
We used to solution to set a fixed as told in the post above and after that the problem went away. At least for the last 4 days.
Is this LazyWriter not fixed in SP2 for itanium or what?
I recently tried Re-Installing SQL Server 2005 and setup fails with the following error message. I found a forum that instructed me to make sure that the Distributed Transaction Corrdinator Service is started and that the NT AUTHORITYNetworkService is set under the Log on as tab. I verified both of those settings in my services snap-in under the management console. Looks like setup is failing during this section: Integration Services - Configuring Components...
------------------------------
Microsoft SQL Server 2005 Setup
Failed to install and configure assemblies C:Program FilesMicrosoft SQL Server90DTSTasksMicrosoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087
Error message: Unknown error 0x80131501
Error description: You must have administrative credentials to perform this task. Contact your system administrator for assistance.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29549&EvtType=sqlca%5csqlassembly.cpp%40Do_sqlAssemblyRegSvcs%40Do_sqlAssemblyRegSvcs%40x80131501
An unknown error (-50) occurred.
So I can not recieve my email.
Running Entourage Student Ed.2004, Mac OS 10.5!
does any know what this means:
View 3 Replies View RelatedI'm on SQL Server 2005 SP2.
I have the old age question of how to process a string parameter that is passed to a Stored Procedure that has an unknown number values. The example below has 5 values but it could be anywhere between 1 and 20.
I basically need to extract each value to Insert these values into the appropriate tables.
In the SQL 2000 days I use to do this with some T-SQL code that determines where the comma is and then I get the value and so on.....
I have read somewherethat this can be achieved using the XML Data Type.
Can someone show me that or atleast get me started on how to achiev this?
DECLARE @Range VARCHAR(200)
SET @Range = '10, 4, 8, 6, 22'
Hi All,
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @cmd, no_output
SET @email = 'mailsend -f someone@mymail.com -d -smtp -t someone@mymail.com -sub "Error Log Errors" -m J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @email, no_output
Thanks.
Hi All,
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @cmd, no_output
SET @email = 'mailsend -f someone@mymail.com -d -smtp -t someone@mymail.com -sub "Error Log Errors" -m J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @email, no_output
Thanks.
How can I get all SQL commands in SqlServer? How can I trace this commands?
Thansk
Hello,
I finally got access to Northwind/pubs.
I would like to know when and why do we use the following set commands. The SQL Server BOL does not say why and when to use these commands.
Thanks in advance!!!
sqlnovice123
Option Default Setting
Set nocount OFF
Set rowcount 0
Set ansi_nulls ON
Set quoted_identifier ON
I have a package which imports text files in a directory. The problem I am having is that the text files have differing numbers of source columns. Can anyone example me some script which handled differing numbers of source columns for a dts package...
View 1 Replies View Related