Posts by buddylee17

There are several ways to do this. Fortunately you at least have a primary key.

Be smart and backup the database before attempting.

Here's an example solution using a CTE:

Create the test tables and populate:

CREATE TABLE PassportTest(
     Id int not null
    ,Name varchar(25) not null
    ,IdentityNo1 char(5) null
    ,IdentityNo2 char(5) null
    ,PassportNo char(5) null
)

INSERT PassportTest (Id, Name, IdentityNo1, IdentityNo2, PassportNo)
SELECT 1, 'John', 'E6788', 'A9383' , null
UNION ALL
SELECT 2, 'James', null, null, 'E8364'
UNION ALL
SELECT 3, 'John', 'E6788', 'A9383' , null
UNION ALL
SELECT 4, 'John', 'E6788', 'A9383' , null
UNION ALL
SELECT 5, 'James', null, null, 'E8364';

Now delete the dupes:

;WITH Keepers as (
    SELECT MAX(Id) Id
        ,Name
        ,IdentityNo1
        ,IdentityNo2
        ,PassportNo
    FROM PassportTest
    GROUP BY Name
        ,IdentityNo1
        ,IdentityNo2
        ,PassportNo
)
DELETE FROM PassportTest
WHERE Id NOT IN (
    SELECT Id FROM Keepers
);

The master database has a scalar function, fn_varbintohexstr, for this.

Example:

DECLARE @text varchar(256), @varbinary varbinary(256);
SET @text = 'A1B2C3';
SET @varbinary = cast(@text as varbinary(256));

SELECT 
     @text OriginalString
    , sys.fn_varbintohexstr(@varbinary) HexString /* returns nvarchar(max)*/

This article was written in 2007 but still applies: http://www.search-this.com/2007/03/12/no-margin-for-error/
Priceless information for anyone in CSS hell.

Use the bit datatype for boolean. Valid values are 0 or 1. In the below example, Active is the bit column with a default value of 1 (true).

CREATE TABLE ActiveUsers (UserId int NOT NULL, Active bit NOT NULL default 1)

/*Register User*/
INSERT ActiveUsers (UserId) VALUES (123) --Whatever the userid is. Active will be set to 1 because that's the default value.

/*Delete User*/
UPDATE ActiveUsers SET Active = 0 WHERE UserId = 123

I'm assuming by menu, that you mean a drop-down menu populated from a web application. Being a programmer, you'll have the tendency to perform iterations over result sets. This will perform very poorly when your row count gets higher. Save the iterations for after the db gives you back the results. Moreover, think in SETS.

Your algorithm is correct, however this will not be optimal with a relational database. A relational database can retrieve 1 row as fast as it can 1000. However, retrieving 1000 rows, 1 row at a time will be very slow.

Get the entire result set from the db, then let the app format the presentation layer (query assumes the table name is Menu):

SELECT ROW_NUMBER() OVER (ORDER BY Parent.name) MenuId, Parent.name Level1, ChildFirst.name Level2, ChildSecond.name Level3
FROM Menu Parent
	LEFT OUTER JOIN Menu ChildFirst ON Parent.id = ChildFirst.parent
		LEFT OUTER JOIN Menu ChildSecond ON ChildFirst.id = ChildSecond.parent
WHERE Parent.parent IS NULL

The Left outer join is only required because there is no certainty that every parent has a child. Additionally, the ROW_NUMBER function is only available in sql 2005 and up.

You can output to excel using OPENROWSET or bcp (with xp_cmdshell).

Try modifying the following to pull from the table, rather than comparing the date variables:

DECLARE @intime DATETIME
DECLARE @outime DATETIME
DECLARE @time int--total seconds
DECLARE @days int, @hours tinyint, @minutes tinyint, @seconds tinyint
SET @intime = GETDATE()
SET @outime = DATEADD(S, 3663, @intime)

SET @time = DATEDIFF(S,@intime,@outime)
SET @days = @time / 86400
SET @hours = (@time/3600) - (@days * 24)
SET @minutes = (@time/60) - (@days * 1440) - (@hours * 60)
SET @seconds = @time % 60

SELECT @time total_second_count
	 , @days day_count
	 , @hours hour_count
	 , @minutes minute_count 
	 , @seconds second_count

urtivedi has given you 2 good examples that will work.

In SQL Server 2008, there's a 3rd option which utilizes the new date datatype:

SELECT * from tablename where
CAST(starttime AS date) = CAST(endtime AS date)

http://msdn.microsoft.com/en-us/library/ms186724.aspx#DateandTimeDataTypes

What is the point of the routesequence?? You're just storing the same value twice...

Also, each row should be unique, whether it be an identity on the id or a composite value. Rows 1 & 2 are duplicates, as are 6 & 7. This design violates first normal form, will be slow to query, and a nightmare to maintain.

Here's a simple example of how to use output variables in stored procedures:

CREATE PROC dbo.ReturnString 
    @string VARCHAR(500)
   ,@outputString VARCHAR(500) OUTPUT
AS
SET @outputString = @string
GO

Then to call the stored procedure, you'll want to declare a variable and assign the value like so:

DECLARE @output VARCHAR(500)
EXEC dbo.ReturnString 'output string',@outputString = @output OUTPUT
PRINT 'Here''s your output string: ' + @output

Post your connection string

This will work with 2008. With 2005 or earlier, you'll need to use datetime and the convert to varchar to strip the time portion off.

DECLARE @yesterday DATE
SET @yesterday = DATEADD(D,-1,GETDATE())

You can't insert into 2 tables with one statement. You can grab the identity of the parent table and insert into the child table with 2 statements:

DECLARE @AppValId int

INSERT Products (Item)
VALUES ('Pepsi')

SET @AppValId = @@IDENTITY

INSERT ProductAttributes (ApplicationValueID,Attribute,Value)
VALUES (@AppValId,'Price','2.99')

Your subquery is returning multiple rows. This could be fixed with a join:

update ML
set ML.rptprobtype = MD.[Problem Type]
FROM tbl_M_MasterLogs ML
JOIN tbl_rpt_MasterData MD ON ML.CaseNo = MD.CaseNo

If your SQL Server is 2005 or greater, you can use EXCEPT and INTERSECT to find these.
List email addresses that are in both Table1 and Table2:

SELECT email FROM Table1
INTERSECT
SELECT email FROM Table2

List email addresses that are in Table1 but not in Table2:

SELECT email FROM Table1
EXCEPT
SELECT email FROM Table2

Assuming SQL Server 2005 +.

The maintenance plan is typically an ssis package stored either in the database or on the file system. Yes, you can edit these packages just like any other SSIS package.

Expand Management-> Maintenance Plans and double click the plan. The plan should open for editing. If not, you'll need to export the package and edit in BIDS.

It's not difficult if you are already familiar with SSIS. Having experience in .net development will be a big plus as well, as 2005 supports vb.net and 2008 supports both vb.net and c#.

See here: http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urldecode.aspx

You could also create a CLR function utilizing the HttpUtility.UrlDecode Method.

The easiest way to bring the db back online would be to drop it then attach the db, specifying the correct data and log files. Dropping the db is key, because it will clear the db's info from the master and insure that your attachment will be clean.

ALTER DATABASE [DBName] SET OFFLINE
DROP DATABASE [DBName]

I'd recommend attaching this database to a test server (with a copy of your data and log files) before dropping it from production. This will verify that the data and log files aren't corrupt.

Use a GROUP BY with the HAVING clause. Haven't tested, but it should work.

select PREMISEADDRESS1 from PREMISE PREM
inner join STATUS ST on PREM.LOCATIONID = ST.LOCATIONID
inner join SUPPLIES SUP on ST.STOPID = SUP.STOPID
group by PREMISADDRESS1
HAVING COUNT(SUP.ITEMID) > 10

http://msdn.microsoft.com/en-us/library/ms180199.aspx

You have to declare the variable @letter and assign it a value:

DECLARE @letter CHAR(1)
SET @letter = 'A'
SELECT First_Name FROM dbo.Names WHERE First_Name LIKE '%' + @letter + '%'

Use BETWEEN and cast the varchar to date:

select * from mytable where dtcreated BETWEEN CAST(fromdate AS DATE) and CAST(todate AS DATE)

Edit: This works on SQL Server 2008 and up. Cast to datetime if you are using an earlier version.

Defining the column as Identity, means that each value is unique and will never repeat. It is an identifier for the row.

There are many ways to export data:
1.) Export data wizard
2.) SSIS
3.) bcp utility
4.) clr stored procedure
.
.
.
It's all up to what you are comfortable with.

The management studio is an exe. Your machine is probably grabbing the R2 ssms instead of the 2008 one. The 2008 exe will probably be in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

As for the express instance, you'll need to connect to it.
Server type: Database Engine
Server name: MachineName\SQLEXPRESS

The server name is in MachineName\InstanceName format.

Also, use services (Start->Run->services.msc) or the SQL Server Configuration Manager to verify that the instance you want to connect to is running. The parentheses will tell you the instance name.

Yes, you can install multiple instances of sql server on the same box without any issues. It's fairly common for developers to install Sql Server Express with Visual Studio and then later install a full version of Sql Server.

Right click the connection manager and select properties. Scroll through the properties until you see Expressions. Add an expression for the Connection String property.

"C:\\filename " + 
(DT_WSTR,4)YEAR(@[System::StartTime])
    + RIGHT("0" + (DT_WSTR,2)MONTH(@[System::StartTime]), 2)
    + RIGHT("0" + (DT_WSTR,2)DAY(@[System::StartTime]), 2) + ".csv"

will return C:\filename 20110128.csv. The date in the filename will be the date the package is executed.

First, please use code blocks when posting code examples.

Example:

[code] --put your code here [/code].

Second, you are using a cursor to loop through and populate dynamic sql. Cursors should be a last resort and the same goes for dynamic sql. This code will be a nightmare to maintain and debug.

That being said, there are occasions that you'll have to use them. I'll assume this is such an occasion.

I'd recommend starting off by replacing Declare @execstring nvarchar (4000) with Declare @execstring nvarchar (MAX) . I have seen issues with dynamic sql where the exec string was too long and got truncated which caused the query to not compile.

I'd also recommend commenting out your execute statements and substituting a PRINT statement. This will give you the static sql string for each loop which you can then paste into the query editor and see what the issue is.

Invalid object name 'KIL1011.dbo.11213 '. Is there an object named 11213 in KIL1011? Try:

Select * From sysobjects Where name like '%11213%'

If this query returns no results, that means the object doesn't exist and you need to take another look at your code.

Post back and let me know how this goes.

There's your problem. The database engine, Integration services, and Analysis services all run independently as separate services. You'll need to locate SQL Server and start it. The description should be : Provides storage, processing and controlled access of data, and rapid transaction processing.

If SQL Server is not present in services, this means you didn't get the database engine installed and you'll need to install it.

See more here: http://social.technet.microsoft.com/wiki/contents/articles/a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server.aspx

Open services (start->run->services.msc) and verify that SQL Server is started.
Since the database is on the same machine that you are connecting to, shared memory would be preferred but any of the 3 should work for connecting.

The width and height are in the properties panel.

Also, make sure you are viewing the reports in IE. I don't know why they can't make the reports cross browser compatible, but they won't render or export correctly in other browsers.

Sql server is designed to be ran on a server, and that's why it says server name. It's the computer name. If you have multiple instances of sql server installed, then it will be computer name\instance name. Also, since the management studio and the database engine are both on the same PC, you should be able to use localhost as the server name.

Not sure about the unique part, as a unique constraint will only support one null value in the column. It sounds like you potentially could have many.

Use a check constraint to verify both aren't null:

ALTER TABLE TableA ADD CONSTRAINT CK_BothDepartsNotNull
    CHECK (departA IS NOT NULL OR departB IS NOT NULL)

Use osql:
Start->Run->cmd

This will connect to the default instance on your local machine and return the instance information:
osql -E -q "SELECT @@VERSION"

http://msdn.microsoft.com/en-us/library/aa213088%28v=sql.80%29.aspx

Here's my constructive criticism: You tried something new and played with it until it worked. It accomplished your goals, and hopefully you learned alot. Congrats.

In the future:
Cursors are a last resort. They are slow and don't scale well. Dynamic SQL is also a last resort (and only with the use of sp_executesql). A normalized design, with proper foreign keys, would result in a single, set based update statement that can be cached by the query analyzer and will insure consistent, reliable data.

I hope you don't interpret this the wrong way, as I'm a DBA and these things are red flags to me. It's common for programmers to want to jump into sql and use cursors to iterate through result sets. But, in general, set based operations perform better with a RDBMS:

"Writing a database cursor is like going to the bank and depositing a million dollars, one dollar at a time, using a million tiny deposits. In the set-based solution, you just hand over a million one dollar bills at once. The same work is done (the money is counted and deposited) but the teller will not have to have a separate conversation with you for each piece of paper. The performance difference is obvious, and you are saving 999,999 deposit slips to boot. Typically a set-based solution will perform a magnitude better than an iterative solution and will scale significantly better." Paul Nielsen and Louis Davidson: SQL SERVER MVP DEEP DIVES

This logic is better placed in a stored procedure that can be called from the application. There is no reason to loop through a result set and send a sql command across the network for each individual update.

UPDATE perclientreport
SET Bill = (Extrausage * 1024)/100
WHERE Bill > 0

The database engine runs under a user context. You can open services (Start->run->services.msc) and scroll down to SQL Server (MSSQL Server). Note the user under 'Log On As'. This user must have permissions to that directory.

Can you retrieve the query that is erroring from profiler?

Your best bet is probably log shipping, where you restore the full database to the other server once, then at the interval you specify, the transaction log is backed up and restored to the other db. You'll need to be in full or bulk-logged recovery model for this. You'll also need to leave the secondary server in standby (making it read only).

You could also do a differential backup every 4 hours. You'll still have to restore from the full and the differential, but it will definitely cut down on both the impact to the production environment and the amount of network traffic. The differential will only be the amount changed since the last full backup. So if your full backup is 20GB and 10% of the database has changed over 4 hours, the differential will be around 2GB and will not take nearly as long to backup.

--This will tell you about error 8114
SELECT description FROM master..sysmessages
WHERE error = 8114 AND severity = 16 AND msglangid = 1033

Try wrapping your code in try catch to get more details.

Example:

BEGIN TRY   
   --This will cause an error
   DECLARE @number int
   SET @number = 'ABC'
END TRY

BEGIN CATCH
   --Capture the error info
   SELECT ERROR_NUMBER() AS ErrorNumber
	  ,ERROR_SEVERITY() AS ErrorSeverity
	  ,ERROR_STATE() AS ErrorState
	  ,ERROR_PROCEDURE() AS ErrorProcedure
	  ,ERROR_LINE() AS ErrorLine
	  ,ERROR_MESSAGE() AS ErrorMessage;	    		 
END CATCH

That's an Access database. This is the MS SQL Server forum. Repost in the Access forum: http://www.daniweb.com/forums/forum128.html

Open SQL Server Management Studio and right click the instance (probably "ServerName\SQLExpress"). Select properties. Select Connections. Verify that enable remote connections is checked.

Open SQL Server Configuration Manager. On the left, click SQL Server Network Configuration -> Protocols for SQLEXPRESS. Enable TCP/IP and Named Pipes.

Post back if you still can't connect.

No, your Begin/End nesting is wrong. The Delete @Items statement should be the last line in the While loop (before END).

The DELETE statement has 2 purposes:
1. Remove the row after you are done with it.
2. SET THE WHILE condition to FALSE once all rows are deleted and stop the loop.

Use a table variable and loop through it like an array:

DECLARE @ItemName VARCHAR(10)
DECLARE @Address VARCHAR(100)
DECLARE @Items TABLE (ItemName VARCHAR(10), Address VARCHAR(100))
INSERT @Items
	SELECT '95B046047' , '8 MALLORY GDNS'
	UNION ALL
	SELECT '95B045912' , '1157 OCONNOR DR'
	UNION ALL
	SELECT '95B045902' , '28 SENTINEL RD'
	UNION ALL
	SELECT '95B045901' , '20 BROOKWELL DR'
	UNION ALL
	SELECT '95B044899' , '22 BROOKWELL DR'
	UNION ALL
	SELECT '95B046066' , '4140 BATHURST ST'
	UNION ALL
	SELECT '95B046065' , '142 WELLESLEY St E'
	UNION ALL
	SELECT '95B046064' , '433 SILVERSTONE DR'
	UNION ALL
	SELECT '95G030658' , '435 SILVERSTONE DR'
	UNION ALL
	SELECT '95B046069' , '437 SILVERSTONE DR'
	UNION ALL
	SELECT '95B046068' , '262 SHERBOURNE ST'
	UNION ALL
	SELECT '95B046067' , '8 Humber Blvd'
	
--Loop through the table variable, treating it like an array
WHILE (SELECT COUNT(*) FROM @Items) > 0
	BEGIN
		--Grab first row from table and assign values to local variables
		SELECT TOP 1 @ItemName = ItemName, @Address = Address FROM @Items
		
		PRINT @ItemName + N' ' + @Address
		
		--Delete the row from the table variable
		DELETE @Items WHERE @ItemName = ItemName AND @Address = Address
	END

This should be fine. You will require a little more disk space. Ability to audit is more important than disk space though.

As long as you don't rewrite your application to query this column regularly, you probably won't notice a difference in performance. If you do, modify your indexes to cover the new column.

An Excel Database file? Interesting...

You are storing duplicate information. Both tables contain Date, TeamId, and MemberId.

You need a table for member, team, and a teammemberlink table.

Access based forms only work on the desktop. You need to make a web based form with a web development language (asp.net, asp, coldfusion, php, jsp, ror,...)

Well, lets see.

Many users can be associated with a certificate
and
Many certificates can be associated with a user

This is simply a many to many relationship.

Here's a suggestion:

CREATE TABLE Users(
userid int identity not null primary key, username varchar(25) unique not null
)
CREATE TABLE Certs(
--use smallint or int for certid if you need to support more than 255 certs
certid tinyint identity not null primary key, certification varchar(10) unique not null
)
CREATE TABLE CertUserLink(
--certified column represents the Y/N value. 1 is true, 0 is false
userid int not null, certid tinyint not null, certified bit not null default 0
)

ALTER TABLE CertUserLink
   ADD CONSTRAINT PK_certuserlink PRIMARY KEY CLUSTERED (certid,userid)    
   
--Make sure that the userid and certid are valid before inserts can be made
ALTER TABLE CertUserLink
   ADD CONSTRAINT FK_certs FOREIGN KEY (certid) REFERENCES Certs(certid)
ALTER TABLE CertUserLink
   ADD CONSTRAINT FK_users FOREIGN KEY (userid) REFERENCES Users(userid)

Replace DateColumn with your actual date column

SELECT CAST(DateColumn AS DATE) AS DateOnly

Also, if you are interested in formatting the date, there are many ways to do this.
http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/