Saturday, September 21, 2013

[T-SQL] Restore Database - is File= optional?

[T-SQL] Restore Database - is File= optional?

Restore Database - is File= optional?

Posted: 20 Sep 2013 03:41 PM PDT

Hello, I am currently working with SQL Server 2008 R2 SP1 I am trying to find out if anyone knows whether the "FILE=" is optional in a database restore for SQL. Currently i've done online restores, offline restores, online page restores etc. I've noticed that this option usually specifies FILE=1 which i am assuming means file 1. Most of the databases i've worked with only have one filegroup. My other assumption is that you can restore pieces of database backups such as FILEGROUP=1 FILE=2 to restore the second file in filegroup 1. this seems pretty powerful.My next question is, if this option is left blank what should happen? I've read the technet article found here:http://technet.microsoft.com/en-us/library/ms186858(v=sql.105).aspxas well as the naming conventions here:http://technet.microsoft.com/en-us/library/ms177563(v=sql.105).aspxI would just like to know if what im reading and what my understanding of that reading is correct. I just dont think i am understanding correctly what the definition of what is in chevron's <> means.Thank you, Tac

SQL Agent Job Error - String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)

Posted: 20 Sep 2013 04:39 PM PDT

I'm running into the following message, "String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)" when running a sql agent job. I'm attempting to execute a stored procedure through the job. Keep in mind that when I run the stored procedure in a normal query window, it works fine and only fails when running it as a scheduled job. My guess is that it has to do with how SQL Jobs execute procedures (especially long procedures) but I have no idea how to fix this. If I use Set Ansi_Warnings OFF, the job will work fine, however, I don't know what other issues this may cause.Thanks for the help.

Comparing records against multiple rows

Posted: 20 Sep 2013 03:57 AM PDT

I'm completely lost on how to do this. I basically want to do a CASE statement that looks at multiple rows.I need look at all the people based on what the main person has (RelCode = 18). Example for Sue: Case when Joe Smith's MedBen = M and Sue's MedBen = '' then SpBen = D-DExample for Kate: Case when Jay Evans's MedBen = M and Kate's MedBen = M then SpBen = DSample data:[u]SubsNum | FName | LName | PlanNum | MedBen | RelCode | SpBen[/u]123 ++++| Joe ++++| Smith | FH +++| M ++++| 18 ++++|123 ++++| Sue ++++| Smith | DH +++| +++++| 19 ++++|123 ++++| Ale ++++ | Smith | DH +++| +++++| 19 ++++|245 ++++| Jay ++++| Evans | FH +++| M ++++| 18 ++++|245 ++++| Kate ++++| Evans | FH +++| M ++++| 19 ++++|245 ++++| Mike ++++ | Evans | FH +++| M ++++| 19 ++++|[code="sql"]CREATE TABLE [dbo].[tbl_SubscriberTest]([SubscriberNum] [varchar](50) NULL,[SSN] [varchar](11) NULL,[FirstName] [varchar](50) NULL,[LastName] [varchar](50) NULL,[PlanNum] [varchar](50) NULL,[MedBen] [varchar](1) NULL,[DenBen] [varchar](1) NULL,[RelCode] [varchar](3) NULL,[SpBen] [varchar](4) NULL) ON [PRIMARY] INSERT [dbo].[tbl_SubscriberTest] ([SubscriberNum],[SSN],[FirstName],[LastName], [PlanNum],[MedBen],[DenBen],[RelCode],[SpBen]) VALUES( '1234', '1111', 'MILAN','WHITEHURST','DENT','', 'D','01',''), ( '1234', '2222', 'NICOLAS','WHITEHURST','DENT','', 'D','19', ''), ( '1234', '1234', 'MONIQUE','WHITEHURST','DENT','', 'D','18',''), ( '2468', '2468', 'WILLIAM','CARPENTER','FCNB', 'M','D','18',''), ( '2468', '3333', 'ALEXANDRIA','CARPENTER','FCNB', 'M','D','19',''), ( '2468', '4444', 'SHAYLENA', 'CARPENTER','FCNB', 'M','D','19', ''), ( '2468', '7894', 'JOANN','CARPENTER','FCNB', 'M','D','01',''), ( '3692', '3692', 'JOE','WALKER','FHL','M', 'D','18',''), ( '3692', '9632', 'JOHN','WALKER','DENT', '', 'D','19',''), ( '3692', '3574', 'JAMES','WALKER','DENT','', 'D','19',''), ( '3692', '7531', 'JAKE','WALKER','DENT', '', 'D','19', '')[/code]Does that make sense? In the data from the SQL code,anyone with a RelCode of 18 is the main employee. So if the MedBen for the main employee is M but any of the other people in that group of same SubscriberNum have '' for MedBen then I'd need to change the SpBen to D-D. Example: Joe, John, James, and Jake Walker would have D-D for SpBen but the Whitehurst family would just have D since Monique's MedBen field is blank.I'm desperate!
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog