Need help creating a temporary table in MS SQL Server.

Hello,

I am working on a webapp using VB.net

Right now I am writing to a sql table during a process where the end userstarts entering the contents for a file that is going to be generated once hefinishes entering the data, but the problem is that if more than one user isdoing the same process the data would get mixed up. To avoid this Ithought in creating a temporary table (its name will consist of a stringand the current date time).

I would like to see any tutorialabout creating and working with temp tables. Or if you have anysuggestions, I will appreciate them. Thanks

There are two types of temp tables in SQL Server # local temp table scope very limted but also the most used because it uses less resources and SQL Server may clean up if you forget it, the other ## global temp table longer scope but you must drop it explicitly or it can harm your application. Try the link below for some tricks of how to use the temp tables, I am assuming you know they are created in the Temp Db. Hope this helps.

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1

Need Help creating a SSIS Package (Newbie)

Following is what I would like to do, so I can keep updating my central SQL Server database with latest updates from the field. I like to use SSIS 2005 to create a package that could do this. Any help to get me started would be appreciated. I need some help soon, pls give me something to get started. APpreciate it. Thanks.

Open connection and read client location table on the local SQL Server database called PODO
For each location id in the table do the following:
Store locationid/clientid in a variable called CLLOC_ID
Construct file name with mdb extension and store in a variable MDB_FILE
Establish connection to the data import folder
Search for that MDB_FILE in the folder on the file system
If there is a file where match = true then do this:
1) Open the access database
2) Read and import the data from the customer experience table
3) Write that data to the SQL Server tables where location = CLLOC_ID
4) Exit process
IF there is no match, exit process
Keep looping until all the client ids/loc ids are read from the SQL Server client location table.

MA

SSIS won't "search" for files as such. You have to tell it where the files are. If you have a collection of .mdb files in a folder then you could loop over them using the ForEach loop.

Import data from Access can be done in a data-flow

There are a number of ways of discovering if the incoming data has a matchinng PK in the destination (which I think is what you are trying to do). Have a look at the LOOKUP component.

-Jamie

|||

Jamie:

Thanks for the advice. I would like to loop thru a SQL Server table and build file names for the mdb files , and then use those file names to match with what i have in the folder. For each file i construct, i would like to establish a connection to that file, and import data. How would i implement this functionality?

MA

|||

Here's how: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

-Jamie

|||

Hi Jamie:

I went ahead and added the ForEach Loop container, and would like to see if that is the right starting point for what I am trying to achieve. Can you give me like a step by step, on how to do this, since I am having a real tough time figuring out how to do things like build a filename from the data retrieved from the SQL execute task. Also then how do i pass that file name further down the process, and how do i create something that would take that built filename and compare it with the file names found in the actual folder that i am connecting to on my local hard drive. I have setup two connections using the connection manager, one is for the SQL Server, while the other one is right now pointing to one dummy access file in the folder. I would need to somehow make the connection manager dynamically look for filenames, and once a match is found, import data. The import data part is pretty straight forward, but i havent yet gotten there. The bigger issue is that i need to build a file name list on the fly by reading thru a sql server table, and then comparing the filenames with what i have in the folder on the local drive. If a match exisits, then i would like to continue with a data import process. Since I am extremely new to SSIS, and I am not a DBA/Database developer, i am more of an application developer on the front end, can you please give me a quick step by step directions. This will help me get started, and if i can figure out things like how to pass output from one sql statement to another task, and how to do comparisons within SSIS, and then how to dynamically read thru files with those variables, this will help in the future, as I will be building more of these type or ETL processes with much more advanced setup. I appreciate your time, and so far I really appreciate your help. Thanks and I look forward to your reply, and I know I am asking too much quesitons, but this is a real project, and I am up against a deadline. Thank you.

MA

sql

Need help converting date format.

Hi and thanx for reading my post..

I have a reg_date field in my MSSQL DB which is formatted like this :

dd.mm.yy tt:mm:ss (eks. 24.12.03 18:00:03)

What i want to do is get the 8 first chars from this string so i end up with only : 24.12.03

Have tried different variations of : convert(char(8) but not sure how i do this really..

Have already searched the net for a solution but had to post it since i didn't find anything useful..

Hope someone can help me out

Best regards
Mirador-/select ((CONVERT(CHAR(6),getdate(),104)) + RIGHT(CONVERT(CHAR(10),getdate(),104),2))|||select (CONVERT(CHAR(10),getdate(),4))

using 104 adds the century to the year, which then has to be eliminated in the first response. Just using 4 returns only the last two digits of the year. Be carefule though, as this type of date format will be a problem in your code in 96 years ;-)|||Sorry, make that CHAR(8).|||Hi and thanx for the quick reply !

Have tried the code but still got trouble with this errormsg i get :

--> Incorrect syntax near '.03.'

This is the code :

SELECT bla1, bla2, bla3, bla4 FROM TABLE WHERE blabla = 1 and blabla = 0 and CONVERT(char(8), reg_date, 4) LIKE " & strTheDate.

ps!.. (strTheDate is asp variable..)

I have debugged the strTheDate and i know it's the correct format : 24.12.03 ex.

ps!.. the field is a DateTime format, just to ensure that :)

Best reagards
Mirador|||Can;t say I think this is the best way of doing this, but I haven't had enough coffee to do a re-write (and blindman will undercut me anyway).

Try this (note the single quotes!):

SELECT bla1, bla2, bla3, bla4 FROM TABLE WHERE blabla = 1 and blabla = 0 and CONVERT(char(8), reg_date, 4) = CONVERT(char(8), CAST('" & strTheDate & "' AS DATETIME),4)

This will convert strTheDate to the same format.

Need help converting a select query into a case statement

I have the following query:

(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')

I need to convert this into a case statement.
I tried various ways but did not get the result that I was after

Thanks,
LauraConvert a SELECT statement into a CASE statement?

I haven't a clue what you are intending to do.|||This is the query that I am dealing with:

SELECT PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice AS InvoiceNumber,
CASE WHEN (AR.InvoiceDate = '')
THEN '0'
ELSE DATEDIFF(day, AR.InvoiceDate, ********* need to insert case statement to find the end of the period ************** ) END AS DaysOld,
SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount WHEN LedgerAR.TransType = 'CR' AND
LedgerAR.SubType = 'T' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) AS InvoiceBalance,
SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'I' THEN - 1 * LedgerAR.Amount ELSE 0 END) AS Interest,
SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'R' THEN LedgerAR.Amount ELSE 0 END) AS Retainage,
SUM(CASE WHEN (LedgerAR.WBS2 <> '9001' AND AR.Period = '200408') THEN - 1 * LedgerAR.Amount ELSE 0 END) AS total
FROM PR LEFT OUTER JOIN
AR ON AR.WBS1 = PR.WBS1 AND PR.WBS2 = '' AND PR.WBS3 = '' LEFT JOIN
LedgerAR ON AR.WBS1 = LedgerAR.WBS1 AND AR.WBS2 = LedgerAR.WBS2 AND AR.WBS3 = LedgerAR.WBS3 AND
AR.Invoice = LedgerAR.Invoice LEFT JOIN
LD ON PR.WBS1 = LD.WBS1 AND PR.WBS2 = LD.WBS2 AND PR.WBS3 = LD.WBS3 LEFT JOIN
CFGDates ON CFGDates.Period = AR.Period
WHERE (LedgerAR.TransType = 'IN') AND (LedgerAR.SubType <> ' X ' OR
LedgerAR.SubType IS NULL) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND (LedgerAR.Amount <> 0) AND
(AR.PaidPeriod > 200408) AND (PR.WBS1 = '001-298') OR
(LedgerAR.TransType = ' CR ') AND (LedgerAR.SubType IN (' R ', ' T ')) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND
(LedgerAR.Amount <> 0) AND (PR.WBS1 = '001 - 298 ')
GROUP BY PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice, Ar.Period, CFGDates.AccountPdEnd
HAVING SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) <> 0

Within the DateDiff function I need to find the end of the period using a case statment such as

Case When AR.Period='200408' Then Min(CFGDates.AccountPdEnd) Else '0' End As EndPeriod

The problem is that I am having a hard time integrating this into the top query. By using the case statement that I wrote above, I get an error stating that there is a problem near 'As' . I am wondering if there is a way to perform a case statement within a case statement.

Hope that clears things up.

Thanks,
Laura|||that's right, you cannot assign an alias to a value if that value is inside a function

just drop the As EndPeriod

oh, and note that you are looking for the difference in days between AR.InvoiceDate and the value of this case expression, so make sure not to use quotes around the zero if indeed you want december 31, 1899, which seems rather unlikely, but that is, i think, the date that corresponds to zero|||Here is a CASE statement that incorporates your logic:

CASE WHEN (AR.InvoiceDate = '') THEN '0'
WHEN AR.Period='200408' THEN DATEDIFF(day, AR.InvoiceDate, Min(CFGDates.AccountPdEnd))
ELSE DATEDIFF(day, AR.InvoiceDate, 0)
END AS DaysOld

...but whether it will integrate with the rest of your SQL statement, I do not know.|||blindman, that's the same as the nested version (you are allowed to nest CASEs, you know)

that date equal to an empty string gives me the creeps

and the quotes around the zero don't make me feel too hot either

also, i just noticed, CFGDates.AccountPdEnd is in the GROUP BY, so why even bother finding the MIN for it in the DATEDIFF, eh|||Oh, I certainly didn't mean to imply that was ALL that was wrong with her code.

What gives me the heebie-jeebies about it is all the hard-coded values.

Need help converting a CHAR into a Datetime

I have a Database which is having a Counterdate time Column stored in the form of Char(24)

But i need it to be in form of datetime so that i can use the datetime functions on it..When i use the cast or convert inside the function where i am passing this character it gives me error

"Conversion failed when converting datetime from character string."

I am done all permutatiions and combinations for this used

Set @.DE = convert ( datetime, @.ts,121)

Set @.de = cast( @.ts as datetime)

BUT ALWZ give me same error.... also when i copy the whole of the data table into some other database the error doesnt come.. i converts the character into the datetime..

I DONT understand why the Server is behaving wiered..

Hoping to get an answer soon.

With regards

Sharad

Database Developer ,

UIC

Hi Sharad,

Can you post a sample of the (character) data that is causing the issue?

Thanks,

Rob

|||

Sir,

I am just using the performance Logs of the a server and in that the database get automatically generated and hence..

I get the data of datetime column in Char(24) form.. and when i use an function to convert that into a datetime form it gives me error..

take anytime .e.g of the form

"2006-11-15 17:33:22.015"

if this is the time stamp and stored in the char(24) format and I want it to be datetime ...

What any sensible person would do

1. cast it into datetime

2. Convert into datetime..

Both not working ..

With regards

Sharad

|||

PLZ reply if u could Help with this simple STUFF

This CODE I RUN

DECLARE @.DS char(24)

SEt @.ds = '2006-10-27 20:01:13.297'

print @.ds

declare @.de datetime

Set @.de = convert(datetime, @.ds, 121)

print @.de

Output is

:::

2006-10-27 20:01:13.297

Oct 27 2006 8:01PM

But i want output to be exactly same

as 2006-10-27 20:01:13.297

Plz help me with this..

|||

change your statement as below,

DECLARE @.DS char(24)

SEt @.ds = '2006-10-27 20:01:13.297'

print @.ds

declare @.de datetime

Set @.de = convert(datetime, @.ds, 121)

print convert(varchar, @.ds, 121)

Need help constructing stored procedure

Hi,

I need to construct a stored procedure that will accept a set of comma seperated numbers. What I would like to do is something like this

create procedure shopping_cart(@.contents as varchar) as
select dvd_title
from movie_dvd
where dvd_detail_id in (@.contents)

dvd_detail_id is defined as int in the table.
The problem is if I declare @.contents as varchar, the procedure only recognizes the first number and ignores the others. Does anyone know how to get around this?

Thanks

Dan@.array is your @.contents...you need to pass the separator too..it can be a ',' or '-' etc

Create procedure shopping_cart (@.Array varchar(1000), @.separator char(1) ) AS
set nocount on
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
declare @.separator_position int -- This is used to locate each separator character
declare @.array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
while patindex('%' + @.separator + '%' , @.array) <> 0
begin
 -- patindex matches the a pattern against a string
 select @.separator_position = patindex('%' + @.separator + '%' , @.array)
 select @.array_value = left(@.array, @.separator_position - 1)
 -- This is where you process the values passed.
 -- Replace this select statement with your processing
 -- @.array_value holds the value of this element of the array
 select Array_Value = @.array_value
 -- This replaces what we just processed with and empty string
 select @.array = stuff(@.array, 1, @.separator_position, '')
end
set nocount off
GO

hth|||I did the following on some other project

create procedure shopping_cart(@.contents as varchar) as
select dvd_title
from movie_dvd
where CharIndex(',' + Cast(dvd_detail_id as varchar(3)) + ',', @.contents) > 0

In the above case you should be sure that the values passed in are going to have , next to it. For eample, it should be ,1,2,3,

Does the above make sense?

AP

Need help constructing Full Names

Hi,
I have three fields that make up employees' names in the database i.e.
FirstName, MiddleInitial and LastName fields. FirstName and LastName fields
are varchar(30) and varchar(50) respectively. MiddleInitial field is char(1)
.
When I use the FirstName + ' ' + MiddleInitial + ' ' + LastName AS
EmployeeName construct, I get no data because most records have a null value
for MiddleInitial field.
If I ask for FirstName + ' ' + LastName AS EmployeeName, I get results as
desired.
How can I take data from all three fields and return EmployeeName as single
field in a stored procedure? Even if I make it work, I'll have two spaces
between FirstName and LastName if the employee has no MiddleInitial due to
null value in that field. I'm sure there's a civilized way to do this. I'd
appreciate some pointers here.
Thanks,
SamYou need to use COALESCE, ISNULL or CASE in your SELECT query:
CREATE TABLE Names
(
FirstName nvarchar(20) NOT NULL
,MiddleInitial nchar(1) NULL
,LastName nvarchar(30) NOT NULL
)
INSERT INTO Names (FirstName, MiddleInitial, LastName)
VALUES ('John', NULL , 'Doe')
INSERT INTO Names (FirstName, MiddleInitial, LastName)
VALUES ('Alfred', 'E', 'Neuman')
SELECT
FirstName + ' ' + COALESCE(MiddleInitial + ' ', '') + LastName
AS EmployeeName
FROM Names
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:2F5162E6-A0C5-47F9-8252-A5EA8243C76F@.microsoft.com...
> Hi,
> I have three fields that make up employees' names in the database i.e.
> FirstName, MiddleInitial and LastName fields. FirstName and LastName field
s
> are varchar(30) and varchar(50) respectively. MiddleInitial field is char(
1).
> When I use the FirstName + ' ' + MiddleInitial + ' ' + LastName AS
> EmployeeName construct, I get no data because most records have a null val
ue
> for MiddleInitial field.
> If I ask for FirstName + ' ' + LastName AS EmployeeName, I get results as
> desired.
> How can I take data from all three fields and return EmployeeName as singl
e
> field in a stored procedure? Even if I make it work, I'll have two spaces
> between FirstName and LastName if the employee has no MiddleInitial due to
> null value in that field. I'm sure there's a civilized way to do this. I'd
> appreciate some pointers here.
> --
> Thanks,
> Sam|||Great... Thank you very much Tibor.
--
Thanks,
Sam
"Tibor Karaszi" wrote:

> You need to use COALESCE, ISNULL or CASE in your SELECT query:
> CREATE TABLE Names
> (
> FirstName nvarchar(20) NOT NULL
> ,MiddleInitial nchar(1) NULL
> ,LastName nvarchar(30) NOT NULL
> )
> INSERT INTO Names (FirstName, MiddleInitial, LastName)
> VALUES ('John', NULL , 'Doe')
> INSERT INTO Names (FirstName, MiddleInitial, LastName)
> VALUES ('Alfred', 'E', 'Neuman')
> SELECT
> FirstName + ' ' + COALESCE(MiddleInitial + ' ', '') + LastName
> AS EmployeeName
> FROM Names
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:2F5162E6-A0C5-47F9-8252-A5EA8243C76F@.microsoft.com...
>|||Also you might want to have a look at concat_null_yields_null parameter
in BOLsql