SP_help_revlogin And Default DB Output

May 29, 2008

In Sql Server 2005 when I run sp_help_revlogin the output for sql login will miss the comma [,] after SID and before Default Database statement.

I am wondering if this is a bug, as I don't see any reason why we need to update the output with comma's before Implementing script to other servers.

-- SQL Login: abcbatch sp_help_revlogin output

CREATE LOGIN [abcbatch] WITH PASSWORD = 0x0100C85E1233A8F44083013B3AF1016B7A343535138D4CF6B4D36 HASHED, SID = 0x76C302C1FF59D311A8BA01237B1838B DEFAULT_DATABASE = [EDM], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

[Comma missing before default]

View 3 Replies

SP_help_revlogin

Jun 7, 2006

Dear Folks,

I am in the process of migrating side-by-side from 7 to 2005 and I am wondering if the same sp_help_revlogin will work from 7 to 2005. I want to keep the same pwd, sid etc or do I need to use SSIS (Integration Services).

Thanks

View 3 Replies View Related

Sp_help_revlogin

Mar 20, 2007

Hi, I want to take a copy of all my SQL users on a weekly basis and save the results of the query to a txt file with the date the query was executed as part of the filename. I know I can use sp_help_Revlogin ot backup my users but I can't figure out how to do the rest.

Can anyone help?

View 1 Replies View Related

SP: Default Value For Output Parameters

Feb 8, 2005

When I call a proc with one input parameter and two output parameters, (all the three parameters having defaults in the proc), I was expecting to see these values defaulted to in the proc. But apparently, this not the case. Could someone tell me what am I doing wrong here? Appreciate your time.

USE Pubs
GO

CREATE PROCEDURE dbo.MyTestProc
(@InputParamVARCHAR(30) = 'Input',
@OutPutParam1VARCHAR(30) = 'OutputParam1' OUTPUT,
@OutPutParam2VARCHAR(30) = 'OuputParam2' OUTPUT)
AS
BEGIN
SELECT @InputParam, @OutPutParam1, @OutPutParam2
END
GO

-- Call to the proc
USE Pubs
GO

DECLARE @I1VARCHAR(30)
DECLARE @P1VARCHAR(30)
DECLARE @P2VARCHAR(30)

SET@I1 = 'PassedInput'

EXECdbo.MyTestProc
@I1,
@P1 OUTPUT,
@P2 OUTPUT

SELECT@I1, @P1, @P2

EXEC dbo.MyTestProc

View 3 Replies View Related

Sp_help_revlogin--URGENT

Aug 19, 2002

I am trying to figure how to run the above stored procedure as a daily job and have the output sent to a file, so that I can copy the file and later run it on another server. How do I have the output saved as a file ? Can anyone help with this??

View 1 Replies View Related

Default Date Output Format

Sep 19, 2001

Hi Kids,

I've just upgraded a 6.5 database to version 7.0 and I'm having a problem with default date output formats.

Under 6.5 the output format was: Sep 2 2001 12:00PM
Under 7.0 the same field is output as: 2001-09-02 12:00:00.000

Is there a way to permanently set the default output format for dates on a database or server wide?

Thanks
Ron

View 1 Replies View Related

A Rewrite Of The Sp_help_revlogin Procedure (use At Own Risk)

Mar 21, 2006

Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).

I have altered the sp_help_revlogin (from Microsoft article # 246133 )

PLEASE TEST/FIX before you use this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_help_revlogin_2005]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE sp_help_revlogin_2005 @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @logintype char(1)

DECLARE @logindisabled int

DECLARE @binpwd varbinary (256)

DECLARE @txtpwd sysname

DECLARE @tmpstr varchar (256)

DECLARE @SID_varbinary varbinary(85)

DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name <> 'sa' and type in ('S','U','G')

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin_2005 script '

PRINT @tmpstr

SET @tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

PRINT 'DECLARE @pwd sysname'

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@logintype = 'G' OR @logintype = 'U')

BEGIN -- NT authenticated account/group

IF @logindisabled = 1

BEGIN -- NT login is denied access

SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

PRINT @tmpstr

END

ELSE BEGIN -- NT login has access

SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

PRINT @tmpstr

END

END

ELSE IF (@logintype = 'S')

BEGIN -- SQL Server authentication

SELECT @binpwd = password_hash FROM master.sys.sql_logins WHERE SID = @SID_varbinary

IF (@binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @binpwd, @txtpwd OUT

SET @tmpstr = 'SET @pwd = CONVERT (nvarchar(128), ' + @txtpwd + ')'

PRINT @tmpstr

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name

+ ''', @passwd = @pwd, @sid = ' + @SID_string + ', @encryptopt = ''skip_encryption'''

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name

+ ''', @passwd = NULL, @sid = ' + @SID_string

END

PRINT @tmpstr

END

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

View 4 Replies View Related

Default Output Encoding For Management Studio 2005

Feb 26, 2008

Hi All,

Not sure if this is the right place to ask but..

Our company has just migrated to 2005 from 2000. I've got Management Studio doing much of what I need it to do from the old enterprise manager (which I wish I could go back to).

My problem is this.

When saving CSV files from a script run inside Management Studio (ad hoc reports, custom queries, etc) the default encoding is to UNICODE. This produces files that excel doesn't handle very well (most of the people I'd be sending the results to would freak out when the CSV file doesn't appear as expected).

I've discovered that I can change the encoding to ANSI when I save the query but as it's a manual process I'm sure I'm going to get tired of it very quickly. I would really like to make ANSI encoding the default.

(at the moment and for the foreseeable future, we don't need the functionality that Unicode provides).

Anyone know how to do this -- I've tried searching the docs without success.

Thanks All,

Charlie.

-------------
Charlie

View 1 Replies View Related

SQL Server 2012 :: Use Of Default Keyword As Parameter Default - What Value Is It

Aug 11, 2015

@pvColumnName VARCHAR(100) = Default,

However, I am unable to determine what is the value for Default. Is it '' ?

Default is not permitted as a constant - below fails to parse:

WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)

View 4 Replies View Related

Number Of ROWS Of Output Of Aggregate Transformation Sometimes Doesn't Match The Output From T-SQL Query

Dec 25, 2006

While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.

For example,the output of the Aggregate Transformation may be 960216 ,but the

'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.

I'm sure the Group By of the Aggregate Transformation is right!

But ,when I set the "keyscale" property of the transformation,the results match!

In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.

Thanks for your advice.

View 2 Replies View Related

Transact SQL :: Generic Store Procedure Call Without Output Parameters But Catching Output

Sep 21, 2015

Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])

EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]

I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working

DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'

[code]...

View 3 Replies View Related

Output And Error Output Write The Same Table At The Same Time, Stall The Process.

Aug 30, 2006

Hi

I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....

The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.

What can I do to avoid such situation?

Thanks in advance,

JD

View 4 Replies View Related

Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation

Dec 8, 2006

I am working on an OLAP modeled database.

I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).

I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.

The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.

What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?

I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.

Thanks in advance for any assistance you can provide.

View 9 Replies View Related

Query Produces Jumbled Output / Output Not In Sequence

Jul 23, 2005

Hi!Server info -Win2K3 Server +SP1 with 1 GB Memory and 1.5 GB Virtual MemorySQL Server 2000 Enterprise Edition + SP3 running on this.Required result -Create a SQL Script that will contain a set of create, update, insert& delete statements (about 17500 lines including blank lines) thatcan be run on different databases seperatelyHow we do this -We have a SP - that creates a temporary table and then calls anotherSP that actually populates the temporary table created by the first SP*Samples of both SPs are below -PROBLEMThe result is directed to a file -However when the query is run it runs through the entire script but'Jumbles' the outputRunning the same scripts on a copy of the database on other machineswork fine and the size of the outfiles is exactly the sameI have increased the page size to 2.5 GB and restarted the server.Running the sp now generated the correct output a few times but gotjumbled as before after a few more users logged in and activity on theserver increased.Another interesting point is that the output is jumbled exactly thesame way each time. It seems the sql executes correctly and writesthe output in chunks only writting the chunks out of sequence - butin the same sequence each time.e.g. of expected resultInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table3Values ................Update RefGenSet Last = 1234Where RefGenRef = 1JUMBLED OUTPUTInsert into Table1Values x, y, z, 1, 2Insert into Table1Values q, s, g, 3, 4Insert into Table1Values c, d, e, 21, 12....Insert into Table2Values ...Insert into Table2Values ...Values ...Update RefGenSet Last = 1234Where RefGenRef = 1Insert into Table3Values ................Insert into Table1Values c, d, e, 21, 12....Insert into Table2----------------------------------------Sample of First Script - STATDATA_RSLT**************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOSET NOCOUNT ONGOCREATE PROCEDURE StatData_rsltASCREATE TABLE #tbl_Script(ScriptText varchar(4000))EXEC TestStatData_intSELECT t.ScriptTextFROM #tbl_Script tGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************************Sample of CALLED SP - TestStatData_int*******************************************SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE TestStatData_intASDECLARE @AttrRef int,@TestID int,@PrtTestRef int,@AttrType tinyint,@EdtblSw tinyint,@NmValRef int,@SrtTypeRef int,@AttrStr varchar(20),@TestStr varchar(20),@PrtTestStr varchar(20),@AttrTypeStr varchar(20),@EdtblStr varchar(20),@NmValStr varchar(20),@SrtTypeStr varchar(20),@TestRef int,@Seq int,@PrtRef int,@Value varchar(255),@TermDate datetime,@AttrID int,@DefSw tinyint,@WantSw tinyint,@TestRefStr varchar(20),@SeqStr varchar(20),@PrtStr varchar(20),@TermDateStr varchar(255),@AttrIDStr varchar(20),@DefStr varchar(20),@WantStr varchar(20),@LanRef int,@LanStr varchar(20),@Code varchar(20),@Desc varchar(255),@MultiCode varchar(20),@MultiDesc varchar(255),@InhSw tinyint,@InhStr varchar(20),@InhFrom int,@InhFromStr varchar(20),@Lan_TestRef int,@ActSw tinyint,@ActSwStr varchar(20)SELECT @Lan_TestRef = dbo.fn_GetTestRef('Lan')INSERT INTO #tbl_ScriptVALUES('')-- Create tablesINSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_Test (AttrRef int, TestID int , PrtTestRefint, AttrType tinyint, EdtblSw tinyint, NmValRef int, SrtTypeRefint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES('CREATE TABLE #tbl_TestAttr(AttrRef int, TestRef int, Seq int,PrtRef int, AttrType tinyint, Value varchar(255), TermDate datetime,AttrID int, DefSw tinyint, WantSw tinyint, ActSw tinyint)')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('CREATE TABLE #tbl_AttrName(AttrRef int, LanRef int, Codevarchar(20), [Desc] varchar(255), MultiCode varchar(20), MultiDescvarchar(255), InhSw tinyint, InhFrom int)')INSERT INTO #tbl_ScriptVALUES ('')-- insert Test valuesDECLARE Test_cursor CURSOR FORSELECT l.AttrRef, l.TestID, l.PrtTestRef, l.AttrType, l.EdtblSw,l.NmValRef, l.SrtTypeRefFROM Test lOPEN Test_cursorFETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestStr = ISNULL(CAST(@TestID as varchar), 'NULL'),@PrtTestStr = ISNULL(CAST(@PrtTestRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@EdtblStr = ISNULL(CAST(@EdtblSw as varchar), 'NULL'),@NmValStr = ISNULL(CAST(@NmValRef as varchar), 'NULL'),@SrtTypeStr = ISNULL(CAST(@SrtTypeRef as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_Test(AttrRef, TestID, PrtTestRef,AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('VALUES ( ' + @AttrStr + ', ' + @TestStr + ', ' +@PrtTestStr+ ', ' + @AttrTypeStr + ', ' + @EdtblStr + ', ' + @NmValStr + ', ' +@SrtTypeStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM Test_cursorINTO @AttrRef, @TestID, @PrtTestRef, @AttrType, @EdtblSw, @NmValRef,@SrtTypeRefENDCLOSE Test_cursorDEALLOCATE Test_cursorDECLARE TestAttr_cursor CURSOR FORSELECT le.AttrRef, le.TestRef, le.Seq, le.PrtRef, le.AttrType,le.Value,le.TermDate, le.AttrID, le.DefSw, le.WantSw, le.ActSwFROM TestAttr leWHERE le.WantSw = 1AND le.ActSw = 1OPEN TestAttr_cursorFETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@TestRefStr = ISNULL(CAST(@TestRef as varchar), 'NULL'),@SeqStr = ISNULL(CAST(@Seq as varchar), 'NULL'),@PrtStr = ISNULL(CAST(@PrtRef as varchar), 'NULL'),@AttrTypeStr = ISNULL(CAST(@AttrType as varchar), 'NULL'),@Value = ISNULL(@Value, 'NULL'),@TermDateStr = ISNULL(CAST(@TermDate as varchar), 'NULL'),@AttrIDStr = ISNULL(CAST(@AttrID as varchar), 'NULL'),@DefStr = ISNULL(CAST(@DefSw as varchar), 'NULL'),@WantStr = ISNULL(CAST(@WantSw as varchar), 'NULL'),@ActSwStr = ISNULL(CAST(@ActSw as varchar), '1')SELECT @Value = '''' + @Value + ''''WHERE @Value <> 'NULL'INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_TestAttr(AttrRef, TestRef, Seq, PrtRef,AttrType, Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @TestRefStr + ', ' +@SeqStr+ ', ' + @PrtStr + ', ' + @AttrTypeStr + ', ' + @Value + ', ' +@TermDateStr + ', ' + @AttrIDStr + ', ' + @DefStr + ', ' + @WantStr+', '+ @ActSwStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM TestAttr_cursorINTO @AttrRef, @TestRef, @Seq, @PrtRef, @AttrType, @Value,@TermDate, @AttrID, @DefSw, @WantSw, @ActSwENDCLOSE TestAttr_cursorDEALLOCATE TestAttr_cursorDECLARE AttrName_cursor CURSOR FORSELECT e.AttrRef, e.LanRef, e.Code, e.[Desc], e.MultiCode,e.MultiDesc, e.InhSw, e.InhFromFROM AttrName e, TestAttr leWHERE e.LanRef = 0AND e.AttrRef = le.AttrRefAND le.WantSw = 1AND le.ActSw = 1OPEN AttrName_cursorFETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromWHILE @@FETCH_STATUS = 0BEGINSELECT @AttrStr = ISNULL(CAST(@AttrRef as varchar), 'NULL'),@LanStr = ISNULL(CAST(@LanRef as varchar), 'NULL'),@Code = ISNULL(@Code, 'NULL'),@Desc = ISNULL(@Desc, 'NULL'),@MultiCode = ISNULL(@MultiCode, 'NULL'),@MultiDesc = ISNULL(@MultiDesc, 'NULL'),@InhStr = ISNULL(CAST(@InhSw as varchar), 'NULL'),@InhFromStr = ISNULL(CAST(@InhFrom as varchar), 'NULL')SELECT @Code = REPLACE(@Code, '''',''''''),@Desc = REPLACE(@Desc, '''','''''') ,@MultiCode = REPLACE(@MultiCode, '''','''''') ,@MultiDesc = REPLACE(@MultiDesc, '''','''''')INSERT INTO #tbl_ScriptVALUES ('INSERT INTO #tbl_AttrName(AttrRef, LanRef, Code, [Desc],MultiCode, MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('VALUES (' + @AttrStr + ', ' + @LanStr + ', ''' + @Code +''', ''' + @Desc + ''', ''' + @MultiCode + ''', ''' + @MultiDesc +''',' + @InhStr + ', ' + @InhFromStr + ')')INSERT INTO #tbl_ScriptVALUES ('')FETCH NEXT FROM AttrName_cursorINTO @AttrRef, @LanRef, @Code, @Desc, @MultiCode,@MultiDesc, @InhSw, @InhFromENDCLOSE AttrName_cursorDEALLOCATE AttrName_cursor-- Do update TestAttr dataINSERT INTO #tbl_ScriptVALUES ('UPDATE le')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' le.TestRef = t.TestRef,')INSERT INTO #tbl_ScriptVALUES (' le.PrtRef = t.PrtRef,')INSERT INTO #tbl_ScriptVALUES (' le.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' le.Value = t.Value,')INSERT INTO #tbl_ScriptVALUES (' le.TermDate = t.TermDate,')INSERT INTO #tbl_ScriptVALUES (' le.AttrID = t.AttrID,')INSERT INTO #tbl_ScriptVALUES (' le.DefSw = t.DefSw,')INSERT INTO #tbl_ScriptVALUES (' le.WantSw = t.WantSw,')INSERT INTO #tbl_ScriptVALUES (' le.ActSw = t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM TestAttr le, #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('WHERE le.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Update AttrNameINSERT INTO #tbl_ScriptVALUES ('UPDATE en')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' en.Code = te.Code,')INSERT INTO #tbl_ScriptVALUES (' en.[Desc] = te.[Desc],')INSERT INTO #tbl_ScriptVALUES (' en.MultiCode = te.MultiCode,')INSERT INTO #tbl_ScriptVALUES (' en.MultiDesc = te.MultiDesc,')INSERT INTO #tbl_ScriptVALUES (' en.InhSw = te.InhSw,')INSERT INTO #tbl_ScriptVALUES (' en.InhFrom = te.InhFrom')INSERT INTO #tbl_ScriptVALUES ('FROM AttrName en, #tbl_AttrName te')INSERT INTO #tbl_ScriptVALUES ('WHERE en.AttrRef = te.AttrRef')INSERT INTO #tbl_ScriptVALUES (' AND en.LanRef = te.LanRef')INSERT INTO #tbl_ScriptVALUES (' AND te.LanRef = 0')-- Do update Test the dataINSERT INTO #tbl_ScriptVALUES ('UPDATE l')INSERT INTO #tbl_ScriptVALUES ('SET')INSERT INTO #tbl_ScriptVALUES (' l.TestID = t.TestID,')INSERT INTO #tbl_ScriptVALUES (' l.PrtTestRef = t.PrtTestRef,')INSERT INTO #tbl_ScriptVALUES (' l.AttrType = t.AttrType,')INSERT INTO #tbl_ScriptVALUES (' l.EdtblSw = t.EdtblSw,')INSERT INTO #tbl_ScriptVALUES (' l.NmValRef = t.NmValRef')INSERT INTO #tbl_ScriptVALUES ('FROM Test l, #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('WHERE l.AttrRef = t.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')--DELETE where just updatedINSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t, Test l')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = l.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM t')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE t.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DELETE FROM te')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE te.AttrRef = le.AttrRef')INSERT INTO #tbl_ScriptVALUES ('')-- Insert TestAttrINSERT INTO #tbl_ScriptVALUES ('INSERT INTO TestAttr (AttrRef, TestRef, Seq, PrtRef,AttrType,Value, TermDate, AttrID, DefSw, WantSw, ActSw)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestRef, t.Seq, t.PrtRef, t.AttrType,t.Value, t.TermDate, t.AttrID, t.DefSw, t.WantSw, t.ActSw')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_TestAttr t')INSERT INTO #tbl_ScriptVALUES ('')-- AttrNameINSERT INTO #tbl_ScriptVALUES ('INSERT INTO AttrName(AttrRef, LanRef, Code, [Desc],MultiCode,MultiDesc, InhSw, InhFrom)')INSERT INTO #tbl_ScriptVALUES ('SELECT te.AttrRef, le.AttrRef, te.Code, te.[Desc],te.MultiCode, te.MultiDesc, ')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN 0')INSERT INTO #tbl_ScriptVALUES (' ELSE 1 END,')INSERT INTO #tbl_ScriptVALUES (' CASE le.AttrRef ')INSERT INTO #tbl_ScriptVALUES (' WHEN 0 THEN NULL')INSERT INTO #tbl_ScriptVALUES (' ELSE 0 END')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_AttrName te, TestAttr le')INSERT INTO #tbl_ScriptVALUES ('WHERE le.TestRef = ' + CAST(@Lan_TestRef as varchar))INSERT INTO #tbl_ScriptVALUES ('')-- Insert new rowsINSERT INTO #tbl_ScriptVALUES ('INSERT INTO Test(AttrRef, TestID, PrtTestRef, AttrType,EdtblSw, NmValRef, SrtTypeRef)')INSERT INTO #tbl_ScriptVALUES ('SELECT t.AttrRef, t.TestID, t.PrtTestRef, t.AttrType,t.EdtblSw, t.NmValRef, t.SrtTypeRef')INSERT INTO #tbl_ScriptVALUES ('FROM #tbl_Test t')INSERT INTO #tbl_ScriptVALUES ('')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_Test')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_TestAttr')INSERT INTO #tbl_ScriptVALUES ('DROP TABLE #tbl_AttrName')-- Update RefGenDECLARE @RefGenReflast int,@RefGenRefStr varchar(10)SELECT @RefGenReflast = lastFROM RefGenWHERE RefGenRef = 1SELECT @RefGenRefStr = ISNULL(CAST(@RefGenReflast as varchar), 'NULL')INSERT INTO #tbl_ScriptVALUES('')INSERT INTO #tbl_ScriptVALUES('UPDATE RefGen')INSERT INTO #tbl_ScriptVALUES ('SET Last = ' + @RefGenRefStr)INSERT INTO #tbl_ScriptVALUES ('WHERE RefGenRef = 1')INSERT INTO #tbl_ScriptVALUES ('')GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO*******************************RegardsGlenn

View 5 Replies View Related

PrimeOutput : Difference Between 'Output' And 'output Buffer'

Aug 12, 2005

When overriding the PrimeOutput method in a custom component, you get as parameters the outputIDs and the output buffers (of type PipelineBuffer). using the outputIDs you can get IDTSOutput90 outputs.

View 5 Replies View Related

Date Picker Bug - Drops The Default Value And Displays Default Value As Todays Date

Apr 3, 2008

Hi,
Does anyone have a workaround or know of a fix to this problem:
Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok.
However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.

Any words of wisdom gratefully recieved,

Howard Stiles

View 1 Replies View Related

Default Value

Aug 15, 2007

is it possible to give a default value for a column which is generated as a result of left outer join...
when there is no value for a particular column it shows null is it possible to change give a default value based on its data type?

View 4 Replies View Related

Default Value For SUM

Jan 26, 2004

I have a sql query that sums a database column. When the data in the column in '0' it prints out to my datagrid nothing, blank.

Is there a way in the SQL statment to somehow assign a default '0' value so that it prints out to my datagrid. Here is the SUM part of my sql code

SUM(nonconformance.nc_wafer_qty) as 'wafers'

View 2 Replies View Related

Default Value?

Jan 26, 2006

Hi, I'm creating a dynamic group of values using SELECT and UNION Example: (SELECT Description = 'Changed PhoneNumber from ' + @old_PhoneNumber + ' to ' + @PhoneNumber WHERE @PhoneNumber <> @old_PhoneNumber UNION ALL SELECT Description = 'Changed FaxNumber from ' + @old_FaxNumber + ' to ' + @FaxNumber WHERE @FaxNumber <> @old_FaxNumber UNION ALL SELECT Description = 'Changed EmailAddress from ' + @old_EmailAddress + ' to ' + @EmailAddress WHERE @EmailAddress <> @old_EmailAddress) The problem here is that SQL Server thinks "Description" is an int (by default probably) and gives me an error when I try to assign a string to it. I'm taking that information and using it as a field in a INSERT INTO ... SELECT statement, so I don't think I am able to use a DECLARE statement or if that would even work. Does anyone know how I can make it so that Description is always a varchar?

View 2 Replies View Related

Default Value

Mar 1, 2001

When creating a table the default value field how do I input a default value into this field so when the user does his input it is automatically displayed. I have a yes/no user defined data type and I want the default value to be no.

View 1 Replies View Related

Default Value

Jan 4, 2005

i want a defauld vaule in my query to come for each row(50 row)

say i have address table with column name,street1, state,pin but no column for city---

How do i return a query for each name(50 row) which has a default value of city as 'Miami' for all 50 rows
the city name is not there in databse at all

any help will be appreciated

View 14 Replies View Related

How To Set The Default Value

Nov 7, 2005

I have database in sql server. One field called datesent, i would like to set the default value as now(), i just type in the default value in database design, it didn't work. Please help, many thanks.

View 4 Replies View Related

Default A Sum Into Else

Jan 8, 2013

I find myself coding the following quite often:

Code:
SELECT M.District,
SUM(case when P.PropType = 'T' or M.Status = 'SA' then
V.CurrentTaxable else 0 end),
SUM(case when SUBSTRING(P.Code,1,1) = 'P' then
V.CurrentTaxable else 0 end),
SUM(case when P.PropType <> 'T' and M.Status <> 'SA' and SUBSTRING(P.Code,1,1) <> 0 then
V.CurrentTaxable else 0 end)

[code]...

In other words, I am trying to sum 1 variable into 3 categories depending upon certain conditions. I have to explicitly negate all the conditions from the first 2 SUM's to simulate a default ending else condition. Is there any construct in SQL that will me to do the last SUM if the first 2 SUM's fail without all the negation conditions?

View 8 Replies View Related

Default Value

May 19, 2004

How do you give a Column a default value when creating a DB Table?

View 1 Replies View Related

Default Value In SQL Db

May 11, 2008

I am using SQL Express and Expression Web. A table in my db records holiday requests and has a status field. I want this field set to a default value of 1. The field in SQL (Default Value/Binding) is set to 1 and displays ((1)). If I enter a row manually, it works fine. However, the web form posts data to the database but this particular field results in NULL? I've had to allow NULLS on this occasion to troubleshoot to this point whereas normally for this field NULLS would not be allowed. Any idea why this is behaving as so?

View 2 Replies View Related

Default Value

Mar 23, 2007

How to set default value(0) in particular column

View 4 Replies View Related

Default Value

Jul 20, 2005

How do i setup the table so that the default date enters the currentdate whenever someone enters a record.vj

View 1 Replies View Related

How Do I Get The Value Of A DEFAULT

Jun 29, 2006

Hi

We have a default defined in our database

CREATE DEFAULT [ schema_name . ] default_name
AS constant_expression [ ; ]

How can I ge the value of the constant using SQL ?

Also - anyone know why this is going to be removed from a future version of SQL - we use it for partitioning with replicated clients (long story) - but every table has one column which is bound to this default. We find it *very* handy.

Ta
Bruce

View 12 Replies View Related

Inserting A Default Value

Sep 3, 2006

I have populated an SQLdata table from an XML datasource usinmg the bulk command. In my SQL table is a new column that is not in the XML table which I would like to set to a default value. Would anyone know the best way to do this. So far I can's see how to add this value in the Bulk command. I am happy to create a new command that updates all the null values of this field to a default value but can't seem to do this either as a SQLdatasource or a APP Code/ Dataset. Any suggestions or examples where I can do this.Many thanks in advance

View 1 Replies View Related

Default Value For A Feild

Nov 19, 2006

I would like have the default value for a feild to be the result of a call to
System.Web.Security.Membership.GeneratePassword(12,4)
How do I implement this?
In a trigger? Do you have sample code?
Thank you very much

View 2 Replies View Related

Is It Possible To Set The Default Database

Oct 4, 2007

In SQL Server Management Studio (Express), when you create a new query the database defaults to "Master", and you either have to select the database that you want or write "USE databasename" at the start of your query.Is there any way of setting the default database? I can't seem to find any option to change this from "Master" to the name of the database that I am using all the time.Thank you, Robert.

View 4 Replies View Related

Default Database

Apr 23, 2008

Hello,
I am working on a single server on two databases. whenever i open new query it uses one of the databases out of it i have access to.
but i want other database to be default whenever i open new query as i work on it mostly.
how can i do that?
generalproblem

View 7 Replies View Related

Dropdownlist -default Value!!

Feb 3, 2004

hi ,

need your help please

I have got a dropdownlist which is getting it's items and values from the table with in SQL server , everything works fine.

I just want to be able to select default item , so when the page is loaded the dropdown list default will be that selected item.

as an exmple if the drop downlist items and values are as follow

item ----> value
---------------------
city -----> 1
tokyo -----> 2
london ------>3

these info is imported from the database
and I want by default tokyo to be selected

many thanks

M

----------------------------------------
here is my code:

Dim sqlConnection As SqlConnection
Dim sqlDataAdapter As SqlDataAdapter
Dim sqlCommand As SqlCommand
Dim dataSet As DataSet
Dim dataTable As DataTable

sqlConnection = New SqlConnection("user id=sa;password=testfirm;database=ken_live;server=csfirm03")

'pass the stored proc name and SqlConnection
sqlCommand = New SqlCommand("Select * From _aci",sqlConnection)

'instantiate SqlAdapter and DataSet
sqlDataAdapter = New SqlDataAdapter(sqlCommand)
dataSet = New DataSet()

'populate the DataSet
sqlDataAdapter.Fill(dataSet, "AA")

'apply sort to the DefaultView to sort by CompanyName
dataSet.Tables(0).DefaultView.Sort = "bsheet"
city.DataSource = dataSet.Tables("AA").DefaultView

city.DataTextField ="bsheet" ' what to display
city.DataValueField ="bsheet" ' what to set as value

city.DataBind()

View 15 Replies View Related