What's the best way to do this? I need to generate a 5 digit length string where all the characters are numeric. However, I need to be able to do this 'x' amount of times (user variable) and store this random strings in a database. Furthermore, I can't generate the same string twice. Old strings will be removed after 6 months. Pseud...
I have the following block of PLSQL that succeeds when ln_length is 4000 characters or less but fails with "ORA-01460: unimplemented or unreasonable conversion requested" when ln_length is > 4000 characters. The block is: DECLARE ls_string VARCHAR2(32767); ls_temp VARCHAR2(32767); ln_length NUMBER := 4000; BEGIN ls_stri...
I have 2 tables: "orders" and "visits". in the orders table i'm saving some details about the order including "userIP" and "orderDate". in the visits table i'm saving details everytime a user visiting my web page including "userIP" and "visitDate". i'm using ASP.NET and SQL SERVER 2005. i want to create a statistic table whitin i save t...
Please check out the following query. The SQL isn't as bad as it looks. Basically, we have a fact table and some simple joins to some dimension tables. Then we have a join to a derived table, given the alias ACCOUNTS-DIM-DEP SELECT dw_mgr.fa_trans_fct.period, dw_mgr.fa_trans_fct.asset_cost_company_code, dw_mgr.fa_tra...
Suppose you're updating a table that is the result of an expression, like so UPDATE OPENQUERY( server, 'SELECT a from b' ) SET rem.a = loc.a FROM OPENQUERY( server, 'SELECT a from b' ) rem ,local_table loc WHERE rem.id = loc.id Will that OPENQUERY be executed once, or twice? I believe the same would apply to any kind of u...
I'm in the process of generalizing a Django DB replication app and it uses the statement: SELECT %s FROM %s LIMIT 1 to fetch 1 row and use the Python DBAPI to describe the fields, it works fine with ORACLE and MySQL but, how cross platform is the LIMIT statement? ...
Hi, I'm running Access 2003. I'm using Switch to select date fields based on a boolean criterion: Switch(<criterion>, Date1, 1, Date2) i.e., if "criterion" is true, then return Date1, otherwise return Date2. Date1 and Date2 are Date/Time type columns in a table. The problem is, Switch returns them as Text -- not Date/Time! Is th...
I've designed my SQL CE tables using the built-in designer in VS2008. I chose the wrong names for a couple. I am now completely stuck trying to find a way to rename them. I am refusing to believe that such a feature could have been "forgotten". How do I rename an existing table using the VS2008 designer, or a free stand-alone app? ...
How can I compare the performance of an Index on a table using the Query Analyzer? I would like to run the same query through the Estimated Execution Plan twice, once using the index and once without, and have a good comparison of the table scans/index scans that are generated. I have tried CREATE INDEX..., SELECT..., DROP INDEX..., SE...
I am working with sensitive/private files stored in SQL Server as VarBinary(MAX). Is there a way to tell the database or nHibernate to nullify the column after a period of time after its creation. ...
Can I reset the auto-increment index of a MySQL table so that it starts counting from zero again? I deleted all the rows in the table--how can I reset that counter as well? ...
I have got a bit of 'brain fade' going on this afternoon, so if anyone can help with this mssql query it would be fantastic. I have a table called 'seasons' with three columns (there are more but not relevant to the example): seasonId, date, tariffId SeasonId is a unique key. A date can only have one tariffid, but a tariffId can have m...
In a stored procedure I'm trying to conditionally update a field (like the 2nd line in the SQL statement below) UPDATE [some_stuff] SET last_update = CASE WHEN val = @NewVal THEN last_update ELSE GETDATE() END, val = @NewVal ...but for a text/ntext field. What's the most efficient way to go about doing that? Does it have to be a s...
Hi folks, I've got the following trigger on a table for a SQL 2008 database. It's recursing .. so i need to stop it. After I insert or update a record, I'm trying to then update a single field on that table. Simple. Here's the trigger :( ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate] ON [dbo].[tblMedia] BEFORE INSERT, UPDA...
I want to know whether one SP is referenced anywhere. Currently I am checking using SP_DEPENDS. Is there any other way to check this...? ...
Using SQL Server 2000 Table1 Date Holiday 23-02-2009 0 24-02-2009 1 25-02-2009 0 Table2 ID Date 01 23-02-2009 01 24-02-2009 02 25-02-2009 …, Here I want to skip the date where Holiday =1 Expected Output 01 23-02-2009 02 25-02-2009 How to make a query for this condition? ...
i have the a query which is union of two querries, the resulting query is bringing duplicate records, i dont want duplicate records, i tried putting DISTINCT but getting the same result, can anybody help me rectifying this query. i also want to know whether this query is safe from sql injection...i'll be pasting my query below: ALTER PR...
hello, im tasked to create a virtual database that clients can create from our web application and have them save data to. the problem now is to have it stored for later re-use. im thinking of dynamically creating a DataTable object in c# then convert it to byte[]. now i want to know if this would be practical to save on a database......
I have scenario like, I need to add my join in conditional e.g. When value is 1 then inner join to table a and if value 2 then inner join to table below is my sample query and I am getting error Incorrect syntax near the keyword 'inner' DECLARE @i INT=1 select name from emp if(@i=1) begin inner join a on a.ID=emp.ID end else if(@i=1)...
I created a login with SQL authentication credential in SQL Server 2008. But, I am not able to login using that credential. We followed the same steps in SQL Server 2005 and working without any problem. What could be the problem? Thanks, P.Gopalakrishnan. ...