Suppose my Test table has a timestamp (rowversion) column, and I have the following code:
INSERT INTO Test ...
SELECT @@DBTS as 'TS'
Can I be sure the TS return column value contains the value of the timestamp column of the Test table? I suspect no in general (although it will work "most of the time"), but I would like a confirmation.
If I am correct, and there may be a mismatch, is there any clever way of getting the exact value, except from doing a SELECT WHERE ...?
I have a table that is returning rows from a table query. It seems I have done it before but I cannot seem to get the right procedure to obtain the values. I will paste in the code below in which you will see my bad attempts at accomplishing what I need.
Dim uid As String Dim pw As String Dim em As String, fn, ln, mi As String Dim par As String Dim Field, n, j As Integer Dim JJ As Integer
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
I've created C#.net program (behind code style). when I run it in Internet explorer, the following error occurs in IE window. pls instruct me how to handle and correct this error. And how to initialize the connectionstring... Great thank!
Server Error in '/' Application. -------------------------------------------------------------------------------- The ConnectionString property has not been initialized. 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.InvalidOperationException: The ConnectionString property has not been initialized. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [InvalidOperationException: The ConnectionString property has not been initialized.] System.Data.SqlClient.SqlConnection.Open() +809 CodeBox.BehindCode.getSubject() +80 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0
I have a simple while process to use with a trigger to insert values into another table. IN VB this was simple but the while in TSQL seems a little different. If anyone can point out my flaw greatly appreciated.
while @cnter < @nodays --insert values insert into table values (value1, value2) --then increment counters and repeat set @sdate = @sdate + 1 Set @cnter = @cnter + 1 How or what is the best way to loop back?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped How do you think..and why ?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped How do you think..and why ?
Im new to SQL so please bear with me & help me as to why Im not getting the desired results.
I want to find the difference between two sets of tables that reside in different databases but contain the same data. I ONLY WANT a. records that are only in A but not in B b. records that are only in B but not in A ______________________________________________________________________
Here is what I wrote using something that I found in this forum -
CREATE PROCEDURE RPT_DETAILS AS BEGIN DECLARE @Rowcount AS INT DECLARE @First_Name AS VARCHAR(50) DECLARE @Last_Name AS VARCHAR(50) DECLARE @Id AS INT
CREATE TABLE #Prowess(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50)) CREATE TABLE #SDK(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
INSERT INTO #Prowess SELECT bb.beenumber, be.FirstName, be.LastName FROM beebusiness bb join beeentity be on bb.beebusinessguid = bb.beebusinessguid
INSERT INTO #SDK SELECT cast(sa_ss as INT), first_name, last_name from ml
SELECT @ROWCOUNT = MAX(ID) FROM #SDK PRINT '------------------------------------------------------------------------------------------' PRINT '------------------------COMPARISION REPORT Between Prowess & SDK--------------------------' PRINT '------------------------------------------------------------------------------------------'
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0 BEGIN SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID FROM #Prowess WHERE ID = @ROWCOUNT PRINT ' * '+@First_Name+@Last_Name SET @ROWCOUNT = @ROWCOUNT - 1 END
SELECT @ROWCOUNT = MAX(ID) FROM #Sdk
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0 BEGIN SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID FROM #Sdk WHERE ID = @ROWCOUNT PRINT ' * '+@First_Name+@Last_Name SET @ROWCOUNT = @ROWCOUNT - 1 END
declare @var varchar(50) set @var= 'COLUMNNAME' select ID, a.@var , b.@var from rooper a join jim_rooper b on b.id = a.id join b_rooper bb on bb.id = a.id where a.@var != b.@var
All that Im trying to do here is instead of using a columnname, Im trying to substitute it with a variable so that it can be referenced at multiple places...
Is it correct way to register my CLR library instead of having T-SQL codes (eg, Strored Procedure, Functions and Triggers) in the database in the following case:
Code security: If my Application (in .NET 2.0) and SQL Server Express in same PC and I have to give Windows-Administrator password to my application-user (to install/unistall some other softwares)
HelloI have having trouble displaying some simple columns in ascending order.I know that the database is populated and I can get the more complex code to work if I display like this: SELECT FName, LName, Town, '<a href="' + url + '">' + Site + '</a>' as LinkFROM Names_DBWHERE FName = 'Tom' And url like 'http:%'ORDER BY LName ASCBut I need a simpler view but I can't get it to workI have tried this:SELECT FName, LNameFROM Names_DBORDER BY LName ASCAnd thisSELECT FName, LNameFROM Names_DBORDER BY LName ASC; And This:SELECT FName, LNameFROM Names_DBORDER BY LName 'ASC' What is wrong with this syntax?ThanksLynn
I'm trying to get a year count and a year amount of payments made by a client. Below is my statement, it is not giving a correct count. If a client made more than one payment on the same day it counts it as 1. What can I do to this statement to get correct totals? SELECT DISTINCT Client_ID, DATEPART(year,PaymentDate) AS 'Year', SUM(AmountPaid) AS 'TotalYearlyPayments', COUNT(DISTINCT Payment_ID) AS 'YearlyPaymentCount'FROM tblPaymentsWHERE Client_ID = @ClientIDGROUP BY Client_ID, DATEPART(year, PaymentDate)ORDER BY Client_ID, Year
Hi, I'm building a web application in which I want to prevent SQL injection. I'm using stored procedures, and using queries on my app like this:in my database...create proc createStudy@title varchar(200),@text textasinsert into studies values(@title,@text)goand in my web app...query="createStudy '"+titleBox.Text+"','"+textBox.Text+"'"; //title and text boxes are textboxes, createStudy is a stored procedure in my databaseodmccommand cmd = new odbccommand(query,con);con.Open();cmd.ExecuteNonQuery();But before this I do this code:if (titleBox.Text.Contains("Drop") || titleBox.Text.Contains("Delete")) messageLabel.Text="No permissions to do that";else(...my code)Is this ok to prevent SQL injection?!?
I am getting my source data from another system am storing the SaleAmount of each product in a field the data type of which is [decimal](12, 2).
For some products I am getting an exact match (upto 2 decimal places) as compared with my source data BUT for some other products the value before the decimal places is correct but the 2 digits after the decimal place does not match with the source data :confused:
Even if this sounds stupid, can you please guide me. Am i missing some very basic and common sense thing?
I have a query and I need to check to see if a field is occupied, i.e., it can have anything in it, i just want to see if something is there... this is what I want, but of course, anything isn't the right word here...
I have two tables: 1) Table that holds all available ports. 2) Table that holds users for each port.
There may be times where one user is getting more than one port at a time.
I've built up an ASP .NET page that will display each user its port/s in one table. On another table I want to display all the other available ports which the user doesn't posses and can buy to own.
My problem is where I try to build up the query. I just can't get all the other ports in normal display.
For example, this is what I need:
Ports table: 1/1 1/2 1/3 1/4
Users table: User A , 1/1 User A , 1/2
ASP page display:
------------ User A Holds: 1/1 1/2 ------------
Available: 1/3 1/4 ------------
Of course the Available option is derived from the User Holds query, and just getting the opposit not equal ports, but I just can't get it ! I've tried all kinds of Joins and nesting SELECT queries with no luck.
SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills FROM PersonSkills INNER JOIN....
How can I choose a certain value from PersonSkills.Skills?
for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.
The tables that I have are: PersonSkills: id, ProductID, Skills
Product: id, ProductName
Ive been reading and playing around with this without success.
Hi friends, I've created one procedure.I'm trying to execute that i got the error message like 'Must declare the scalar variable @series'. but i declared it already.Table name starts with SI,dont have the fields like series and hono.I dont know how to correct this error.Please help me out.Here is my procedure.
alter proc procinsertAllFields as begin declare @series varchar(10) declare @hono varchar(5) declare @tabname varchar(8) declare @sql nvarchar(500) if exists (select * from sysobjects where name=ltrim(rtrim('ccno_dir1'))) drop table ccno_dir1 set @sql='create table ccno_dir1(cc_no varchar(20),series varchar(1), hono varchar(10),denom_code varchar(10),i_date datetime,d_date datetime, locked varchar(10),csd_no varchar(10),invoice_no int,invoice_date datetime)'--print @sql exec sp_executesql @sql
declare c cursor for select series=substring(name,3,1),hono =substring(name,4,5),name from sysobjects where name like 'si[1-3]_____' open c fetch next from c into @series,@hono,@tabname while @@fetch_status=0 begin print 'begin' fetch next from c into @series,@hono,@tabname set @sql='insert into ccno_dir1(cc_no,series,hono,denom_code,i_date,d_date,locked,csd_no,invoice_no,invoice_date) select cc_no,series=@series,hono=@hono,denom_code,i_date,d_date,locked,csd_no,invoice_no, invoice_date from '+@tabname print @sql exec sp_executesql @sql
USE CHEC SELECT [DATE_CONVERSION_TABLE_NEW].MONTH, DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth, DAT01.[_@550] AS LoanType, DAT01.[_@051] AS Branch, DAT01.[_@TP] AS ProdTypeDescr, SMT_Branches.[BranchTranType] AS TranType, --SMT_Branches.[AUCode] AS AuCode, Count(*) AS Totals FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] --ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE] ON DAT01.[_@040] = [_@040] INNER JOIN SMT_BRANCHES ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType] WHERE DAT01.[_@040] Between '06/01/2006' And '06/30/2006' And SMT_BRANCHES.[BranchTranType] = 'RETAIL' AND DAT01.[_@051] = '540' --And SMT_Branches.[AUCode] = '1882' And DAT01.[_@TP] = '115' And DAT01.[_@550] = '3' GROUP BY DAT01.[_@051], DAT01.[_@550], DAT01.[_@TP], SMT_Branches.[BranchTranType], SMT_Branches.[AUCode], [DATE_CONVERSION_TABLE_NEW].MONTH, DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH, DAT01.[_@051], DayofMonth ASC, SMT_Branches.[AUCode] ASC --COMPUTE sum(count(*))
DECLARE @fullname nvarchar(50) SET @fullname = (SELECT (OriginalName + ContentType) AS Name FROM Files_Dyn) INSERT INTO Files_Dyn(FullName) VALUES (@fullname) where username = 'user_admin' ...what is the wrong with this query..it is giving 'Incorrect syntax near the keyword 'where'. Please correct me! thanks in advance!
This is a Stored proc I am working on I dont know much about stored procs. This is what I am trying to achieve The proc takes two input parameter and returns one outparameter.
This is what I have
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[GetNextAction]
( @Code char(10), @Track varchar(30), @NextAction ntext output ) AS BEGIN
SET NOCOUNT ON; Declare @ID int; @ID = Select Sequence from [dbo].[Track] where Code=@Code; Declare @nextCode varchar; @nextCode= Select Code from [dbo].[Track] where sequence =(@ID+1); @NextAction= Select nextAction from [dbo].[CaseStage] where Code=@nextCode; return @NextAction;
END GO
I guess you can understand what I am trying in the proc but the rules and syntax are wrong. Please help me with this. Thanks
The documentation saysISNUMERIC returns 1 when the input expression evaluates to a validinteger, floating point number, money or decimal type; otherwise itreturns 0. A return value of 1 guarantees that expression can beconverted to one of these numeric types.(Cut and pasted from books online)Yet the following, one of many, example shows this is not true.select isnumeric(char(9))select convert(int, char(9))-----------1(1 row(s) affected)Server: Msg 245, Level 16, State 1, Line 2Syntax error converting the varchar value '' to a column of data typeint.So, besides filtering every possible invalid character, how do youconvert dirty values without error. I am not concerned that I may loosepossibly valid values or convert suspect strings to 0 (zero). I justwant to run without raising an error
I am loading data from table A into table B. Certain columns in B havecheck constraints. I'd like for any rows from A, which violateconstraints, to be inserted into a third table, C. When the process isfinished, I'll have only good rows in B, and exeption rows in C.I am investigating INSTEAD OF triggers, however my question to thegroup is, is there a better or best practice for this scenario? Thismust be common. Any high-level tips or direction will be highlyappreciated.DAP
Hi,Given 2 tables:Table1:id Auto,int,PrimKeytable2id inttxt nvarchar50Table2:id Auto,int,PrimKeyorder intThe scenario: Table2.order defines how the table1.txt is should be ordered.Table1.table2id contains the id of the order. This cannot be changed :(How do I select all Table1.txt values but ordered by their correspondingvalues of the table2.order field?--Thx,PipHans---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.516 / Virus Database: 313 - Release Date: 01-09-2003
I am not a company programmer but I do like to write programs for myself. I am currently looking at writing a Database program to track my DVD's, CD's Tapes and the likes (as well as quite a bit of other data). While discussing this with others it was suggested that I use MSDE (since it's free) and that way I could learn more about SQL databases at the same time.
When I connected to the MS website to download MSDE I found that it will not be supported with Vista. Since I am planning on getting a new PC which will most likley come with Vista I am now wondering what would be the best way to develop my database.
1. Should I use Access (Since it will only be accessed across my home network)?
2. Is there a version of SQL (that does not require a License fee) for Home use?
