I have a script that needs to extract data temporarily to do extra operations on it, but then doesn't need to store it any further after the script has run. I currently have the data in question in a series of temporary local tables (CREATE TABLE #table), which are then dropped as their use is completed. I was considering switching to ...
When I try my CLR UDF, I am getting this error:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "getFileSize":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture...
I get to dust off my VBScript hat and write some classic ASP to query a SQL Server 2000 database.
Here's the scenario:
I have two datetime fields called fieldA and fieldB.
fieldB will never have a year value that's greater than the year of fieldA
It is possible the that two fields will have the same year.
What I want is all records ...
Is it possible to write a stored procedure (in tsql or other variants) with dynamic parameters like sp_executesql?
i.e. the number and type of parameters in not known in advance and should be built dynamically on client side.
just the way you can pass any number of parameters with any type to sp_executesql.
...
I'd like to do something like this
raiserror(concat('Error in case @isFishy =', @isFishy, ' @isSmarmy=', @isSmarmy, ' @isTasty = ', @isTasty), 10, 1)
--or
raiserror('Error in case @isFishy =' + @isFishy + ' @isSmarmy=' + @isSmarmy + ' @isTasty = ' + @isTasty, 10, 1)
But it just isn't working. How do I accomplish this? I'm in SQL S...
I want to create a DTS Package to pull data from an Oracle table into a SQL2K
table. How can I insert rows that are not already in the SQL2K table and
update rows that already exist in the SQL2K table?
I guess I could truncate and repopulate the entire table or create a
temporary table and then do updates/inserts from the temp table...
I am thinking about creating stored procedures on the fly.
ie running CREATE PROCEDURE... when the (web) application is running.
What are the risks or problems that it can cause?
I know that the database account needs to have the extra privileges.
It does NOT happen everyday. Only from time to time.
I am using sql server and inter...
Ok, there are a million regexes out there for validating an email address, but how about some basic email validation that can be integrated into a TSQL query for Sql Server 2005?
I don't want to use a CLR procedure or function. Just straight TSQL.
Has anybody tackled this already?
...
I am writing a DB upgrade script that will check to see if an index has the right two columns defined. If it doesn't, or if it only has one of them, then I will DROP it (is there a way to ALTER an index?) and then recreate it with both.
...
I have a Stored Procedure that rolls-back a series of operations. I want to call this from within another SP.
The problem is that the inner SP returns a record set with a single value that indicates the degree of success.
This approach worked well and has some advantages in our context, but in retrospect, I would have done it the con...
I have a table that has a column with a default value:
create table t (
value varchar(50) default ('something')
)
I'm using a stored procedure to insert values into this table:
create procedure t_insert (
@value varchar(50) = null
)
as
insert into t (value) values (@value)
The question is, how do I get it to use the defaul...
Suppose I have a table with a numeric column (lets call it "score").
I'd like to generate a table of counts, that shows how many times scores appeared in each range.
For example:
score range | number of occurrences
-------------------------------------
0-9 | 11
10-19 | 14
20-29 | 3
......
Ok: This is some of my table structure that matters here
CaseStudyID int
Title nvarchar(50)
OverrideTitle nvarchar(50)
Part of my procedure
Declare @Temp table(CaseStudyID int,
Title nvarchar(50))
Insert Into @Temp
SELECT CaseStudyID,Title
FROM CaseStudy
WHERE Visible = 1 AND DisplayOnHomePage = 1
ORDER BY Title
Update...
I'm trying to get the value of generated sql inside a stored procedure.
Executing this
DECLARE @sSQL varchar(5000), @retval int
DECLARE @tablename varchar(50)
SELECT @tablename = 'products'
SELECT @sSQL = 'SELECT @retval = MAX(ID)'
SELECT @sSQL = @sSQL + ' FROM ' + @tablename
EXEC (@sSQL)
SELECT @retval
I get ...
I have been working with TSQL in MSSQL for some time now and somehow whenever I have to insert data into a table I tend to use syntax
INSERT INTO myTable <something here>
I understand that keyword INTO is optional here and I do not have to use it but somehow it grew into habit in my case.
My question is:
Are there any implications...
Hi,
I have (simplified for the example) a table with the following data
Row Start Finish ID Amount
--- --------- ---------- -- ------
1 2008-10-01 2008-10-02 01 10
2 2008-10-02 2008-10-03 02 20
3 2008-10-03 2008-10-04 01 38
4 2008-10-04 2008-10-05 01 23
5 2008-10-05 2008-10-06...
I want to implement a simple debug log, which consists of a table into which I insert insightful messages about the current state of a transaction. How do I stop inserts to this table being affected by rollbacks on the transaction that I'm trying to debug? In Oracle I can use PRAGMA AUTONOMOUS_TRANSACTION to ensure that the inserts are d...
Just to clarify, I'm running Sybase 12.5.3, but I am lead to believe that this holds true for SQL Server 2005 too. Basically, I'm trying to write a query that looks a little like this, I've simplified it as much as possible to highlight the problem:
DECLARE @a int, @b int, @c int
SELECT
@a = huzzah.a
,@b = huzzah.b
,@c = ...
I need a date formula in Oracle SQL or T-SQL that will return a date of the previous week (eg Last Monday's date).
I have reports with parameters that are run each week usually with parameter dates mon-friday or sunday-saturday of the previous week. I'd like to not have to type in the dates when i run the reports each week.
The data ...
My SQL is rusty -- I have a simple requirement to calculate the sum of the greater of two column values:
CREATE TABLE [dbo].[Test]
(
column1 int NOT NULL,
column2 int NOT NULL
);
insert into Test (column1, column2) values (2,3)
insert into Test (column1, column2) values (6,3)
insert into Test (column1, column2) values (4,6)
in...