Short Cut To Exec Jobs
Apr 23, 2007any keyword shortcut to exec a sql job?
do i have to exec step individually when there are more than 2 steps?
ADVERTISEMENT
A Short Question ?
Mar 13, 2004how can i convert the varchar value to a column of data type int?
i am trying to do this
declare @w varchar(50)
select @w = col1 from myTable
select colname from mtable
where mtableID in (@w)
but the mtableID is a int
and i always got
Syntax error converting the varchar value to a column of data type int.
thanks for everyone for helping
Two Short Questions
Mar 17, 2004can anyone tell me do i have to use sqldmo in asp.net C# to backup or restore database?
and how can i execute the *.sql file in asp.net C#?
thanks
Cells Too Short
Apr 3, 2008When I get the results from my query, some of the columns labels are too short and look like this "ran....". How do I extend the cells??
Thanks
US Navy - We are fueled, armed, and go for launch.
Short Date?
Mar 13, 2007Hi all. Is it possible to convert the date to shortdate?
select e.hireddate from hremployees e
it returns 2/07/2007 12:00:00 AM
How can i neglect the time?
Thanks
-Ron-
SP2 - The Long And Short Of It
Apr 17, 2007Problems Encountered Upgrading SQL 2005 SP1 to SQL 2005 SP2 (9.0.3042.1):
In Summary:
Maintenance Plans Broken
Database Mail Broken
Reporting Services Report Viewer web part displays US Format dates instead of report design English-Australian. .
In Detail:
Microsoft SQL Server 2005 SP2 was downloaded from Microsoft.com. This download occurred after 5 March 2007 and the file version was 9.0.3042.1 (SQLServer2005SP2-KB921896-x86-ENU.exe)
During the Installation of SP2 by a Domain Administrator an error occurred on the Database Engine. A Hotfix Exception was raised and the setup completed with errors.
The Error Log reported a problem:
----------------------------------------------------------------------------------
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 2047
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29534
Error Description : MSP Error: 29534 Service 'MSSQLSERVER' could not be started. Verify that you have sufficient privileges to start system services. The error code is (1067) The process terminated unexpectedly.
----------------------------------------------------------------------------------
The SQL Server service could not be manually started. An error was reported in the event viewer:
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
It was noted that a new file mssqlsystemresource.ldf had been copied to the %data% folder.
Note: As part of separating data and log files, both the master and mssqlsystemresource databases had been previously moved to another volume %data% whilst their respective log files had been moved to a separate volume also %log%. This had occurred after SP1 was initially installed and months prior to the SP2 install using Microsoft KB articles to detach and reattach the databases. It appears that the SP2 installer does not work correctly if mssqlsystemresource mdf and ldf files are not in the same location.
Whilst the Engine could not be started €“ the new mssqlsystemresource.ldf file was manually moved to the %log% file, overwriting the previous version.
The SQL Server Service was now able to start without any errors.
Since the SQL Server Service has been started two major errors have become apparent:
The following error is recorded when trying to create a new maintenance plan:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'from_msx'.
Invalid column name 'has_targets'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
Also when using the maintenance plan wizard the following error is recorded when the Wizard attempts to save the plan at the end:
TITLE: Maintenance Plan Wizard Progress
------------------------------
Saving maintenance plan failed.
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
------------------------------
Database Mail is also broken on the server. The Database Mail Log reports an €œActivation Failure€? when a test email is sent. (the email does not arrive). The SQL Log and event viewer also report the following message:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'
Another error affects Reporting Services reports that are accessed using the SharePoint V2.0 Web parts ( rsWebparts.cab) . The date picker in the Report Viewer web part now only recognises US format dates. It is important to note that the same reports run through the Report Manager web site work correctly using DD/MM/YYYY (English-Australian ) date format for the report picker.
It is noted that the rsWebparts.cab file was not updated by SQL 2005 SP2 install. However, the behaviour of the Report Viewer web part has definitely been affected by the SP2 install.
Are the first two issues related? Are they due to the fact that mssqlsystemresource mdf and ldf files had been moved to an unexpected location prior to SP2? How can this be fixed?
Are the Reporting Services SharePoint (V2) web parts going to be updated? We are not in a position to upgrade our Portal 2003/SharePoint Servcies V2 installation at the present time.
Long Vs Short
Mar 26, 2008Here is a fairly basic question in which I'd like assistance. In determining a standard for a schema, for simplicity, my thinking is that using the long data type would meet our purposes.
Are there any thoughts on mapping out where we would not need to take advantage of long vs short? I realize the space but for this particular table, space won't an issue.
Thanks!
Short Cut To Updating Table...
Dec 18, 2007Is there a way to short cut through the fields in a table when updating them or inserting?
ie. I have a table with 78 fields and could grow with time. Is there a way to run the UPDATE or INSERT TO [table](blah....) without listing all the table fields?
CountyLong Name And County Short Name
Aug 31, 2004Hello, i have database front end is Access, and backend is sql server. The front end, there are two dropbox, called 'CountyLongName', and 'CountyShortName'. I want to: when i select a countyLongName, there it will pop up the exact CountyShortName in anohter field.
For example, when i click Howard Couty in the 'CountyLongNmae', it will automatically display "HW" in the 'CountyShortName" Field. How can i do that? Thanks.
Combining These 2 Short Stored Procedures
Dec 18, 2003CREATE PROCEDURE MyBooks_Selling
(
@MemberID SMALLINT
)
AS
SELECT * FROM v_BookInfo_Sellers_Extended WHERE MemberID=@MemberID
GO
GRANT EXEC
ON MyBooks_Selling
TO bto
GO
CREATE PROCEDURE MyBooks_Buying
(
@MemberID SMALLINT
)
AS
SELECT * FROM v_BookInfo_Buyers_Extended WHERE MemberID=@MemberID
GO
GRANT EXEC
ON MyBooks_Buying
TO bto
GO
Is there a way to make it so I could combine those 2 prcedures and choose which table i would like to select from based on another input parameter? I tried it that way but it didnt work...so im asking here to make sure
thx
Short Diplay For Datetime Type
Jun 13, 2006Hi all,
I have a view with a datetime column,
when viewing the column data the format is:
" 2006-06-12T12:00:00.0000000+03:00 "
while i'm only want to see the date part
(that is, 2006-06-12, and if possible in the format: dd/mm/yyyy)
now, when i'm using convert( varchar(10), [column_name],101)
i'm getting the format i need but the problem is that i'm
"losing" the date type, is it possible to view it as the
format i stated and still as a datetime type ??
Regards,
Pit
SQL To Find Short, Fat And Stupid People
Jul 23, 2005This is a question concerning query optimisation. Sorry if it's a bit long,but thanks to anyone who has the patience to help - This is my first posthere...If I have two tables: 'tblContact' and 'tblCategory' where categories arelike:Code Name010101 Short010102 Fat010103 StupidThe junction table 'tblConCat' has fields CctConID, CctCatCode to tell mewhich contacts have which category codes. These are nchar(6) fields, if itmakes any difference.If I need to find all people who are short, fat and stupid I can see twoways:Solution One:SELECT tblContact.* FROM tblContact WHEREConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010101') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010102') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010103')Solution Two:Build a helper table which contains the codes I'm looking forSELECT tblContact.* FROM tblContactWHERE ConID IN(SELECT CctConIDFROM tblConCat INNER JOIN tblHelperON tblConCat.CctCatCode = tblHelper.HlpCatCodeGROUP BY CctConID HAVING Count(*)=3)I have tried them both out and although I looked at the query analyzer itprovided more information than I knew what to do with. In practise theyboth provide similar working times (I wait about a second) but I thought thefirst looked rather inefficient and thought my helper table might help.There are about 30,000 contact records, 180 category records and 120,000junction table records.All I am looking for comments on any pros and cons of these two approaches -does one look that bad? The database was migrated from Access where thehelper table really did help, but using SQL Server I might not need it.Thanks again, if you got this far!
View 16 Replies View RelatedEmpty Page 1 On Short Reports?
Mar 16, 2007I have a suite of several SSRS reports that differ essentially on the stored procedure called. All have very the same one-page header block and all have a footer. Those where a large number of pages are produced, page without fault when printed or converted to PDF.
Two page reports often break down on printing, so that page 1 only has the header and a footer and all the detail lines are printed on page2 (together with the footer). However on the screen the report paged correctly.
I have checked all the obvious problems such as page dimensions exceeding that of the print medium. (A series of such problems was fixed earlier in development.)
I have observed this behaviour with SQL2005 SP1 and SP2 (i.e. SP2 did not provide any fix in this area). The host OS for development and production is Windows 2003 server. The tests were done by viewing in IE6 and the fault only became apparent when going to print preview, printing or in PDF production.
This has the appearance of a bug in SSRS hereby intermediate length reports are not handled correctly. It appears to happen when the amount of detail lines just fills page 2. As soon as the report has sufficient lines to be 3 pages long (page 1 is header plus some details lines plus footer, page 2 is full of detail lines plus footer and page 3 is remaining detail lines plus footer then the problem is no longer seen! Where the detail lines will all fit on page 1, then the problem is not seen.
I observe this variation in behaviour since I am able to switch between different sized datasets and thus switch between the different scenarios described above. I should also note that the reports are in landscape mode.
SQL Server Short-circuits WHERE Condition Evaluation
May 5, 2008Hi I am trying to create stored proc and having some performance issue.
Here is the requirement,
Stored Proc parameters A,B
A is required.
B is optional. The default value for B is 0.
Here is the code inside stored proc:
IF (B = 0)
SET B = 1
SELECT * FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.Column1 = TABLE2.Column1
WHERE A= 2008
AND (table2.ColumnB = B or 1 = B)
If I am passing B as 20 I am having performance issue to pull the data.
If I replace B with any other number the query is working fine.
Isnull And Ntext Variables (short Question)
Jul 23, 2005Hello to all,Below the sample code:declare @arg_szMsgText ntextset @arg_szMsgText = isnull(@arg_szMsgText, N'unknown message')Now the error message I get:"The assignment operator operation cannot take a ntext data type as anargument."Explanation:I'm using it in a SP. I was trying thousand things to make it running,and the only way I maked was, I get it when I set the return value fromisnull fucntion to nvarchar or varchar variable.The problem is, the field in DB, where the value of @arg_szMsgText iswritten) is ntext, so I need to use this format.In the description of isnull function (help) is written:----------------------------------------------------Argumentscheck_expressionIs the expression to be checked for NULL. check_expression can be ofany type.replacement_valueIs the expression to be returned if check_expression is NULL.replacement_value must have the same type as check_expresssion.Return TypesReturns the same type as check_expression.-----------------------------------------------------so ite means, I should be able to use this in such a way ...I know I can assign the value in other way (like by if @variable isnull ....), but if there is already function doing this, why not to useit, if it helds the code more clear?Any ideas? Maybe I'm doing something wrong.Matik
View 3 Replies View RelatedSQL 2005 Vs SQL 2000: Where Clause Short-Circuiting?
Oct 12, 2006Hello all, this is my first post, and I think I've found something interesting between sql 2005 and 2000. Thanks in advance for any help you can provide.
I am attempting to migrate a system from SQL 2000(v 8.00.2039 - Enterprise Edition) to SQL 2005 (v9.0.2047 - Standard Edition ), and a few of my most complex queries are generating errors. I was able to isolate the problem to a fairly simple query that works in SQL 2000, but generates an error in SQL 2005.
I believe that the problem has something to do with how short-circuiting is handled in the WHERE clause. I'm not sure if short-circuiting is really going on when these statements are parsed, but it seems to describe the situation well.
To replicate the problem, first create the following table and fill it with values:
----------------------------------------------
create table OrgView (ORGNAME varchar(100), QID int, ANSWER varchar(100))
insert into orgview (orgname, qid, answer) values('org1', 120, '1')
insert into orgview (orgname, qid, answer) values('org2', 94, '2006-06-06')
insert into orgview (orgname, qid, answer) values('org3', 98, 'free kevin')
----------------------------------------------
The second value (org2) contains a date, but the rest do not.
Under both systems, the following contrived query works fine:
SELECT orgname FROM OrgView
WHERE (QID = 94
AND CONVERT(DATETIME, Answer) = '2006-06-06')
It would seem, that when QID = 94, the expression short circuits, and the CONVERT statement doesn't run. This is good, because the CONVERT statement would throw an error whenever 'Answer' doesn't contain a date.
However, if modify the query by adding an OR to the end like this:
SELECT orgname FROM OrgView
WHERE (QID = 94
AND CONVERT(DATETIME, Answer) = '2006-06-06')
or QID=98
then the query fails on SQL 2005 with a 'Conversion failed when converting datetime from character string.' error.
On SQL 2000, the short circuiting works fine, and the above query returns 'org2' and 'org3'.
I know shortcircuiting may not be the best way to describe the set calculus going on in the background, but can anyone explain what is going on here? Is there any way to make SQL 2005 work like SQL 2000 in this specific case?
Thanks,
-Adam
Problem With A Lot Of Inserts In Short Time - SQL Server 2000
Feb 27, 2008Hi!We have SQL Server 2000 on virtual machine (slowly). We run a pair ofstored procedures from ADO.NET 2.0 (WebService):- first procedure: insert into table A a data row with correlationID- next procedure: search for inserted record in tab A bycorrelationID and insert value into tab B with constraint to tab A.The problem is when we try to do this in short time (few minutes)thousands times (5000/6000 pairs in 5/6 minutes) in 100 parallelthreads:query in second procedure cannot find inserted record in firstprocedure.Everything is dirty read. no transactions.Have you any ideas?edi
View 1 Replies View RelatedSsis Security Short Coming, Major Issues
Jan 6, 2006Hello,
I feel like ssis encryption model has a serious flaw. Especially when linked to SQL Agent jobs.
I have posted and others have posted messages about this. Something is plain wrong with ssis encryption keys and password protection. Also, you do not have the choice not to protect the packages. In my case, protecting packages is completely useless.
Here is the story.
After this post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=131340&SiteID=1&mode=1
I created config files for al my packages connections passswords.
Now, by our IT Policy, I had to change again my password and of course, all packages now return multiple errors when I open them.
Hopefully, the config file did its job and the packages are ran anyways by SQL Agent, however, having to manually retype and resave all packages not to have the errors is just a plain hassle. Not to speak about people not using the config files and the correct "Run As" sql agent account.
I stress the fact that in a real world production environment all packages are driven by SQL Agent jobs and MUST run automatically.
Here is the error I get after opening a package after changing my password:
Error 1 Error loading Constants05.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. c:projectsssis packagesssis constantsConstants05.dtsx 1 1
So Why is'nt this key automatically adjusted after Windows NT Domain password Change?
How can I refresh the key, not to have to reype all the packages connections passwords and rebuilding, Checkin-in again all the stuff?
I do not think the solution is "Use an application account which password never changes when you create your ssis packages" however at this time, this is the only solution I can think of.
How do you guys deal with this problem?
I still do not understand the ssis security model I feel it is diconnected from the reality and unpracticable in a production environment like mine.
Thanks
Philippe
Short Circuit Evaluation In Conditional Split Expression
Jun 23, 2006I think I know the answer to this but thought I'd ask anyway.
I have a conditional split to check a column for null values or empty string values. It looks like this:
(!ISNULL(Ballot)) || (LEN(TRIM(Ballot)) > 0)
My question is: Are both sides of the expression evaluated? My testing says yes, because a Null value causes an error. Is there a way to short circuit the evaluation like the || operator in C# or the (less than elegant, and seemingly threatening) OrElse operator in VB? Whats the best alternative:
A slightly more complex expression that turns a null value into an empty string
A script component
Two conditional splits
Two paths out of one condtional split
I went with the first option, here is the expression I came up with:
LEN(ISNULL(Ballot) ? "" : TRIM(Ballot)) > 0
Too Short Keywords Does Not Work Under Full-Text Functionality
Nov 6, 2007Hi, I am novice to Full-Text funcationality.
The problem I met is that SQL server seems not woring with too short keywords (with FREETEXT syntax). The following is the detailed description.
I index two columns, JobDes(ntext) and JobTitle(nvarchar), which mainly store Traditional Chinese(Big5). If I search with
select * from JobWanted where FREETEXT(JobTitle,'asp'), it goes well. But if I replace the 'asp' with 'as', nothing returns. What is the reason? Can I eliminate this limination?
Any help would be appreciated.
Ricky.
Can Exec Select But Can't Exec Sp
Oct 31, 2007I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).
I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.
These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.
Can some one explain why?
SQL Command To Change A Long Date To Short Date
Aug 25, 2006Is there a SQL command that will change a Date&Time string to just a Date?
View 3 Replies View RelatedHow To Convert Long Date Format To Short Date Format In Store Procedure.
Feb 1, 2008E.g, i have a store procedure. The start date is long date (4/15/2007 3:00pm). i want to select the start date with a particular date (short date format 4/15/2006). Thanks in advance.
View 1 Replies View RelatedUsing Exec
Feb 29, 2000When I use EXEC in a stored procedure ( after building complex option logic) it produces an returns an error of 'Access denied' on the underlying tables.
All objects are dbo owned and execute permission has been given to all users.
Can ant one help?
Rob
Exec
Jul 22, 2003When using a SP for getting a recordset is there any issues with using exec like in: rs.open "exec spWhatever"...
Should I use rs.open "spWhatever" or does it really matter performance wise on the SQL server?
Thanks
Exec In My Sp
Nov 1, 2007I am trying to create a awkward sp, just need to know if i can do this somehow, here i piece of the code...
create procedure IM_SP_TrPreProc /*@TableName Varchar(255),*/ @SystemFileName Varchar(255)
---------------------------------------------
--Param1 = Tablename
--Param2 = Systemfilename
---------------------------------------------
as
declare @TableName Varchar(255);--Just For Testing---DELETE!!
declare @Filename varchar(255); --Store Distinct filename
declare @DSNo Varchar(255);-- Use 'set' to execute Var TableName
declare @SumUnits Varchar(255); --Use 'set' to calculate sum of units
declare @SumValue Varchar(255);
Set @TableName = 'TrDs01' -- Testing Only--DELETE!!
------------------------Set Statements using @TableName Var------------------------------------------
Set @DSNo = 'select distinct DataSupplierNo from ' + @TableName
Set @SumUnits = 'select sum(Units) from ' + @TableName
Set @SumValue = 'Select sum(Value) from ' + @TableName
------------------------------------------------------------------------------------------------------
Insert into TransactionMaster([FileName],DataSupplierNo,ImportFileRecordID,FileLoadDate,
UnitsSum,ValueSum,RecordCount)
Select(@Filename),(exec(DSNo)), ................
Just the Bold and underlined bit "exec(DSNo)"..... is this doable in some way? can i use exec to retrieve the value to insert to data supplier. As far as i know i have to do it like this because im using a variable as the table name...
Exec Sql
Apr 28, 2006I need help understanding the syntax of the "exec sql" statement.
i am looking at code that build an sql string such as
sql="exec SOMETHING Session("id")"
or something like that.
then, there is
conn.execute(sql)
My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.
i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.
in particular, the code i am examining is
sql="exec SurveyDelete "&"'" & Session("StudentID") & " ' "
conn.execute(sql)
i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?
MDX And EXEC
Feb 17, 2006Hi,
Can I execute my MDX statement as in the exhibit format!
DECLARE @test VARCHAR(MAX)
SET @test = 'WITH test AS (SELECT * FROM merchants)'
EXEC(@test)
SELECT * FROM test
If I don't use that dynamice sql statement, everything work fine.
Thanks,
Myo
EXEC
Mar 28, 2006Hi,
I have a written a SP to do indexdefrag to all user table indexes in a databases...I had to use dynamic sql so I can reuse this code for any DB...
declare @strsql varchar(500)
set @strsql = ' dbcc indexdefrag('+'''DBName'''+',554556545,3)'
exec (@strsql)
When I execute the above script, I immeaditely see the results in the query analyser like below:
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
3 0 0
Looks like the indexdefrag did not happen since the logical fragmentation is still a high number like 30%.....
Just wondering whats goin on..
Thanks.
Ranga
Get A Return Value With EXEC?
Aug 29, 2007Hi, I have an sql query like this :DECLARE @TableName varchar(200), @ColumnName varchar(200), @EmployeeID varchar(200), @Result varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' + @ColumnName + ' from ' + @TableName + ' where Recid = ' + @CustomerID + '');Print @Result; I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks
View 1 Replies View RelatedExec And Sprocs
Nov 7, 2000is it possible to have a sproc with a input parm of a column name and have this column name be inserted into an exec statement that runs and provides the output as a OUTPUT parm instead of a result set?
i can get the sproc to take the column name as a parm, run the exec, but cannot figure out how to assign the "dynamic sql" output to a OUTPUT variable instead of returning the result set.
EXEC Dynamic Sql
Oct 17, 1999How can i get the result from a dynamic sql like - exec('select ' + fieldName
+ ' from ' + TableName)
Problem With USE And EXEC
Aug 23, 2000I found this statement in BOL and it didn't make it to work.Is anybody out there who ha the same problem?
Database is MASTER.
USE master EXEC ("USE pubs") SELECT * FROM authors