Thursday, August 8, 2013

[SQL Server] Help creating a View

[SQL Server] Help creating a View

Help creating a View

Posted: 08 Aug 2013 02:47 PM PDT

Hi all,This is my first post here so hopefully I'll include everything that's needed. Please also be aware that I am self taught so I may not do things as easy as possible. I'm using SQL Server 2005.I have some data that contains a string with comma separated values in it and I have used a cursor and a loop to load this data and separate it into another table. My problem is that I need this data available in a View so that it is always up to date with the latest values. I can't use a trigger on the table as the database is shared so I can create new tables, views, stored procs etc but I can't alter existing items.Here is some sample test data: create table TempTesting (ID int, TestData varchar(80)) insert into TempTesting values (1122, '11111,222228,33333,44444') insert into TempTesting values (12345, '12345,54321') insert into TempTesting values (876543, '25847,369587,98758,145289,425986,76532') insert into TempTesting values (255, '951458')And a table for the results: create table TempResults (ID int, TestResult int)And this is the code I use to separate the data: declare @string varchar(100) declare @result varchar(10) declare @stringlocation int declare @ID int declare cursor_name Cursor for select ID, TestData from TempTesting open cursor_name Fetch next from Cursor_name into @ID, @string WHILE (@@FETCH_STATUS <> -1) Begin select @stringlocation = PATINDEX('%,%',@string) while @stringlocation > 0 begin select @result = left(@string,@stringlocation-1), @string = right(@string,(len(@string)-@stringlocation)) insert into TempResults (ID, TestResult) select @ID, convert(int,@result) where not exists (select 1 from TempResults where ID = @ID and TestResult = @result) select @stringlocation = PATINDEX('%,%',@string) continue end insert into TempResults (ID, TestResult) select @ID, convert(int,@string) where not exists (select 1 from TempResults where ID = @ID and TestResult = @string) and @string is not null Fetch next from Cursor_name into @ID, @string End Close cursor_name Deallocate cursor_name --delete from tempresultsSo what I need is to be able to create a View which looks like the results table as the data will be changing constantly. Is this possible?

Password Format woes

Posted: 08 Aug 2013 04:42 AM PDT

During my very steep learning curve in trying to implement an ASP.Net membership system, I naively set the passwordFormat in my web.config file to [i]Hashed[/i]. This was before all my data was entered! I then read that Hashed passwords could not be retrieved. Break it to me gently... do I have to start over and re-enter all the data into my DB? Or is there a clever way of undoing my follies?Tx,Martin

Copy certain tables,stored procedures and views from one Database to another

Posted: 30 Apr 2013 04:05 AM PDT

Hello,I am new sorry, hope i can explain this right, I have an old database called database1, which now we have a new database called database2 (these are not the real names lol), database1 and database2 have the same table names, stored procedures etc, but database1 had foreign keys etc, what I want to know is there a way to copy over only certain database tables from one database to another, not copying the entire database but only the tables needed and restore them onto database2? is there a way for doing the same for certain stored procedures and/or views and not all only certain ones? I have SSIS but so far i can only see for data purposes...Thanks in advance

User does not have permission to perform this action.

Posted: 07 Aug 2013 08:06 PM PDT

Hi all,I wrote like this use MYDBgoexec sp_addrolemember 'db_owner', 'myusername'goit is showing thatUser does not have permission to perform this action.please give me solution.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog