Database Discussion Boards

_Maxxx_13-Jul-09 17:33

13-Jul-09 17:33

I didn't know whether to reply to my original question or post new - but as the original was asked (accidentally) under a different user name, I though I'd start a new thread. Apologies to anyone offended by that. (Original Here)[^]

I have sqlpubwiz 1.3 and it fails to script correctly on SQL Server 2008 if there are any foreign keys (or other constraints).
I'm kind of assuming this is something I am doing - as it's a pretty useless tool otherwise -
I have test db with two tables

CREATE TABLE [dbo].[Table_1](
	[id] [int] NOT NULL,
	[num] [int] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Table_2](
	[id] [int] NOT NULL,
	[num] [int] NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table_2]  WITH CHECK ADD  CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY([id])
REFERENCES [dbo].[Table_1] ([id])
GO

ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1]
GO

Then I (try to) script it...

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2> sqlpubwiz script -d myDatabase -S localhost\MYDATABASE -f .\test.txt

and get the error...

Microsoft (R) SQL Server Database Publishing Wizard
Version 10.0.0.0
Copyright (c) Microsoft Corporation.  All rights reserved.

Generating script for database stit1
- Generating script targeted for SQL Server 2005
Gathering list of objects to script
Scripting objects
.....Error: Operation is not valid due to the current state of the object.

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2>

Delete the foreign key and all is well.

I'd really appreciate it if someone could either confirm or deny the fact that this is 'just me' or, in fact, sqlpubwiz is a pile of donkey doo doo

___________________________________________
.\\axxx
(That's an 'M')

Re: sqlpubwiz Command Line Issue.

Josh Handley13-Jul-11 0:20

Josh Handley

13-Jul-11 0:20

I'm able to use the publishing wizard command line to script a database with foreign keys. I was getting the same error but then I found this post:

http://social.msdn.microsoft.com/Forums/en/sqlexpress/thread/ecfd4379-ee88-4a60-81a4-d6cc9542b1ff[^]

which said to make sure that the case of the database name has the correct case. When I fixed that the error went away.

Help with Built-in Database: Visual Web Developer 2008 Express

pstyles113-Jul-09 5:05

pstyles1

13-Jul-09 5:05

Hi Guys!

Am at my wits end! Please help me with any suggestions.

I have recently created a website for my father's Driving School which has database properties built in. I created it using Visual Web Developer 2008 and am now trying to make it possible for his clients to view any lesson dates and times of future lessons. Each client has their own username and password to access their own user area on the site. Obviously I want each client to be able to see their own times but not those of other clients. My father will be inputting the dates and times of all clients lessons himself.

How would I best go about this? My father is fairly IT literate, but would like all dates and times in a central database if possible. Please help. Many thanks.

Re: Help with Built-in Database: Visual Web Developer 2008 Express

_Damian S_13-Jul-09 19:32

_Damian S_

13-Jul-09 19:32

Well, when your father saves the dates and times into the database, have him select a client from a dropdown list (so that he selects a valid client), then when your clients log into the system, store their ClientID in the session state or similar, and when selecting values from your database for lessons, restrict it by ClientID.

eg:

select LessonDate, LessonTime from tblLesson where ClientID = @ClientID

populating @ClientID with the ClientID of the logged in user.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Help required for CTE

Zafar Sultan13-Jul-09 3:33

Zafar Sultan

13-Jul-09 3:33

This is my stored procedure

ALTER PROCEDURE TeamStructure
(
@rootid int
)
AS
WITH MyTeam
AS
(
SELECT *, [Name] AS ParentName, 0 AS TeamLevel FROM tbl_team
WHERE ParentID =@rootid UNION ALL
SELECT Fam.*,MyTeam.Name AS ParentName, TeamLevel + 1 FROM tbl_team AS Fam
INNER JOIN MyTeam ON Fam.ParentID = MyTeam.ID)
SELECT * FROM MyTeam

It will return the complete hierarchical records.
Level 0 will have root member
Level 1 will have 2 members
Level 2 willhave 4 members
Level 3 will have 8 members
or if level =n
members= 2^n
I need to perform the following operation:
for any level if members are less than 2^n the iteration should stop and return the number of members on that level and the number of level
For example if the number of members on level 4 are 12
The procedure should stop and return
level :4
Members :12
but if the number of members on nth level is exactly 2^n the iteration must look for the next level
Can anyone modify this procedure and help me please?
TIA

LucBite13-Jul-09 3:32

LucBite

13-Jul-09 3:32

I have a Questions table in my database where the quuestiions are selected randomly using the following code
"public DataTable getQuestion(string concept,string questiontype)
{//gives the user the question for the concept selected above
string sql = "";
if (concept == "basic")
{
sql = "SELECT Question FROM Questions WHERE ConceptId LIKE 'BC%'AND QuestionType ='" + questiontype + "' Order by NewId()";

}
else if (Concept == "programming")
{
sql = "SELECT Question FROM Questions WHERE ConceptId LIKE 'PC%'AND QuestionType ='" + questiontype + "'Order by NewId()";
}

return dc.load(sql);//dc is an object of data connection class
}
Another question type is of multiple choice.And for this type of question there's an Options Table which I need to get the options of the question selected randomly using the code above. Any idea how I can do that? Please help.

ML Lingwati

Re: Getting data related to what has been selected randomly from a database

Vimalsoft(Pty) Ltd13-Jul-09 3:59

Vimalsoft(Pty) Ltd

13-Jul-09 3:59

Good day LucBite

Can you explain Briefly what is your Problem. i understand what you are trying to do, but can you tell us what exactly is your problem

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: Getting data related to what has been selected randomly from a database

LucBite13-Jul-09 4:16

LucBite

13-Jul-09 4:16

How do I select the options of a multiple choice question, for the multiple choice question that I selected randomly. Remember, I can't select the options randomly because they have to match the question even though the questiion is selected randomly. The questions are from the Questions Table and the options are from the Options Table. I hope I'm clear please help.

ML Lingwati

Re: Getting data related to what has been selected randomly from a database

Vimalsoft(Pty) Ltd13-Jul-09 4:32

Vimalsoft(Pty) Ltd

13-Jul-09 4:32

Good day LucBite

You did Explain Clearly again so that i cannot understand better, remember you showed us a Function that return a datatable, now when i said explain clearly i mean to tell us where does the parameters get passed from(UI or Another Function). I Assume you are trying to do something like this

You have a Testing Application, First you have Different type of Test "Basic", "Programming" and "MultipleChoice"

Now you can select Randomly for "Basic" and "Programming" But you cannot select Randomly for "MultipleChoice" So you want it to be able to select for "MultipleChoice" Questions Randomly"

This is how i understand it ,before i give you a solution , i would like to if am right else explain more give me more info

Re: Getting data related to what has been selected randomly from a database

Blue_Boy13-Jul-09 4:33

Blue_Boy

13-Jul-09 4:33

LucBite wrote:
hope I'm clear please help.

Not definitly not.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: Getting data related to what has been selected randomly from a database

DoctorMick13-Jul-09 6:13

DoctorMick

13-Jul-09 6:13

So, for a multiple choice question type you want to return random questions and, for each of the questions returned, the applicable options?

If I were you, I'd change your sql to return 2 columns for multiple choice, the question text and the id (the primary key on the question table). From there, you can select the options from the appropriate table using the id of the question.

Re: Getting data related to what has been selected randomly from a database

Jerry Hammond13-Jul-09 10:34

Jerry Hammond

13-Jul-09 10:34

LucBite wrote:
How do I select the options of a multiple choice question, for the multiple choice question that I selected randomly. Remember, I can't select the options randomly because they have to match the question even though the questiion is selected randomly. The questions are from the Questions Table and the options are from the Options Table. I hope I'm clear please help.

I'm not absolutely sure I understand your question, but this is what I think you might be asking for:

    class SwitchTest 
{
    static void Main()
    {
        Console.WriteLine("Coffee sizes: 1=Small 2=Medium 3=Large"); 
        Console.Write("Please enter your selection: "); 
        string s = Console.ReadLine(); 
        int n = int.Parse(s);
        int cost = 0;
        switch(n)
        {
        case 1:
            cost += 25;
            break;
        case 2:
            cost += 25;
            goto case 1;
        case 3:
            cost += 50;
            goto case 1;
        default:
            Console.WriteLine("Invalid selection. Please select 1, 2, or 3.");
            break;
        }
        if (cost != 0)
        {
            Console.WriteLine("Please insert {0} cents.", cost);
        }
        Console.WriteLine("Thank you for your business.");
    }
}
    /*
        Sample Input: 2

        Sample Output:
        Coffee sizes: 1=Small 2=Medium 3=Large
        Please enter your selection: 2
        Please insert 50 cents.
        Thank you for your business.
    */

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Splitter container

Isaac Gordon13-Jul-09 0:01

Isaac Gordon

13-Jul-09 0:01

I have to panels in Splitter Container.One panel1 Contain Grid view and panel2 contain form having two textbox and two button.

When I selected row in grid view whice event will throw to return selected values of grid row in form(Textboxes).only to selected row time.......

if any one know answer reply immediatelly............

Re: Splitter container

Muhammad Mazhar13-Jul-09 0:43

Muhammad Mazhar

13-Jul-09 0:43

I think you need to handle SelectionChanged event.

Share your experience with others
Check my Blog...

Can't save new records to the dataset - I'm practising with the binary formatter

BluesEnd12-Jul-09 17:43

BluesEnd

12-Jul-09 17:43

I’m practicing a database and I have a 3-column table. I’m using binary formatting to save/open the file. I can’t see what is wrong with my code –
I can display records in the datagrid view ok when I press the save button
I can also save the records into the dataset ok when the save/close button is pressed.
When I reopen the programme and press the reload button to reload the dataset, additional records will not be displayed nor saved though original records will be showing in the datagrid.
When I reopen the programme and DO NOT press the reload button, I can enter new records and they will be saved because this is basically overwrites the original dataset.
Ideally, I want to be able to open the dataset and then be able to add new records to the dataset/datatable.
Any help and advice on how to correct this?
Thanks,
Steve

Here is my code:

Imports System.IO
Imports System.Runtime.Serialization
Imports System.Runtime.Serialization.Formatters.Binary

Public Class Frm_DataEntry
Dim ThePoint As New Point(540, 150)
Public TheRecord As DataRow
Dim ii As Integer

Private Sub Frm_DataEntry_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.Location = ThePoint
End Sub

Private Sub Tbx01_FirstName_Enter(ByVal sender As Object, ByVal e As System.EventArgs) Handles Tbx01_FirstName.Enter
Tbx01_FirstName.Clear()
Tbx02_LastName.Clear()
Tbx03_Phone.Clear()
End Sub

Private Sub Btn01_Save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btn01_Save.Click
TheRecord = Frm_Mainform.dt_Table.NewRow

TheRecord(Frm_Mainform.dt_Col02_FirstName) = Tbx01_FirstName.Text
TheRecord(Frm_Mainform.dt_Col03_LastName) = Tbx02_LastName.Text

Frm_Mainform.dt_Table.Rows.Add(TheRecord)
Frm01_WithDGV.DGV01.DataSource = Frm_Mainform.ds_Dataset
Frm01_WithDGV.DGV01.DataMember = Frm_Mainform.dt_Table.ToString

Frm01_WithDGV.DGV01_BS_Col01_PKey.DataPropertyName = Frm_Mainform.dt_Col01_PKey.ToString
Frm01_WithDGV.DGV01_BS_Col02_FirstName.DataPropertyName = Frm_Mainform.dt_Col02_FirstName.ToString
Frm01_WithDGV.DGV01_BS_Col03_LastName.DataPropertyName = Frm_Mainform.dt_Col03_LastName.ToString

Dim ii As Integer
ii = Frm_Mainform.dt_Table.Rows.Count
MessageBox.Show(ii)

End Sub

Private Sub Btn02_SaveAndClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btn02_SaveAndClose.Click
Dim Filename As String = "PermitToWork.pmt"
Dim fStream As FileStream

Frm01_WithDGV.DGV01.DataSource = Frm_Mainform.ds_Dataset
Frm01_WithDGV.DGV01.DataMember = Frm_Mainform.dt_Table.ToString

Frm01_WithDGV.DGV01_BS_Col01_PKey.Width = 90
Frm01_WithDGV.DGV01_BS_Col02_FirstName.Width = 125
Frm01_WithDGV.DGV01_BS_Col03_LastName.Width = 125
Frm01_WithDGV.DGV01_BS_Col04_Date.Width = 125
Frm01_WithDGV.DGV01_BS_Col01_PKey.DataPropertyName = Frm_Mainform.dt_Col01_PKey.ToString
Frm01_WithDGV.DGV01_BS_Col02_FirstName.DataPropertyName = Frm_Mainform.dt_Col02_FirstName.ToString
Frm01_WithDGV.DGV01_BS_Col03_LastName.DataPropertyName = Frm_Mainform.dt_Col03_LastName.ToString

If File.Exists(Filename) Then
Try
fStream = New FileStream(Filename, FileMode.Create)
Dim binFormat As New BinaryFormatter
binFormat.Serialize(fStream, Frm_Mainform.ds_Dataset)
Catch anex As ArgumentNullException
MsgBox("The inventory could not be accessed")
Catch ex As SerializationException
MsgBox("The application failed to retrieve the inventory")
Finally
fStream.Close()
End Try
Else
Return
End If

Frm_Mainform.Close()
End Sub

Private Sub Btn03_Reload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btn03_Reload.Click
Dim Filename As String = "PermitToWork.pmt"
Dim fStream As FileStream

If File.Exists(Filename) Then
Try
fStream = New FileStream(Filename, FileMode.OpenOrCreate, FileAccess.ReadWrite)
Dim binFormat As New BinaryFormatter
Frm_Mainform.ds_Dataset = binFormat.Deserialize(fStream)

Frm01_WithDGV.DGV01.DataSource = Frm_Mainform.ds_Dataset
Frm01_WithDGV.DGV01.DataMember = Frm_Mainform.dt_Table.ToString

Frm01_WithDGV.DGV01_BS_Col01_PKey.Width = 90
Frm01_WithDGV.DGV01_BS_Col02_FirstName.Width = 125
Frm01_WithDGV.DGV01_BS_Col03_LastName.Width = 125
Frm01_WithDGV.DGV01_BS_Col04_Date.Width = 125
Frm01_WithDGV.DGV01_BS_Col01_PKey.DataPropertyName = Frm_Mainform.dt_Col01_PKey.ToString
Frm01_WithDGV.DGV01_BS_Col02_FirstName.DataPropertyName = Frm_Mainform.dt_Col02_FirstName.ToString
Frm01_WithDGV.DGV01_BS_Col03_LastName.DataPropertyName = Frm_Mainform.dt_Col03_LastName.ToString

Catch anex As ArgumentNullException
MsgBox("The inventory could not be accessed")
Catch ex As SerializationException
MsgBox("The application failed to retrieve the inventory")
Finally
fStream.Close()
End Try
Else
Return
End If

Frm01_WithDGV.DGV01_BS_Col01_PKey.Width = 90
Frm01_WithDGV.DGV01_BS_Col02_FirstName.Width = 125
Frm01_WithDGV.DGV01_BS_Col03_LastName.Width = 125
Frm01_WithDGV.DGV01_BS_Col04_Date.Width = 125

Dim ii As Integer
ii = Frm01_WithDGV.DGV01.Rows.Count
MessageBox.Show(ii)

End Sub

End Class

Thanks for your help and advice!
Steve

Reporting Services

devvvy12-Jul-09 16:57

devvvy

12-Jul-09 16:57

Hello

I am digging up on Reporting Services asmx interface, reading up articles... I want to know ahead of time, however, if asmx interface allows:
(1) Report Execution, save PDF on disk or shared drive
(2) Report archive after execution
(3) Enumeration of reports

Thanks

dev

sql execution audit - tools?

devvvy12-Jul-09 15:02

devvvy

12-Jul-09 15:02

hello

We need a tool, prefer open source/free, which allows auditing of sql submission and execution. Any recommendation?

Thanks

dev

Re: sql execution audit - tools?

Niladri_Biswas13-Jul-09 16:17

Niladri_Biswas

13-Jul-09 16:17

Apex SQL Audit
http://download.cnet.com/Apex-SQL-Audit/3000-10254_4-10628231.html[^]

http://www.fileshome.com/sql_audit_database_audit_sql_auditing_594.html[^]
Smile | :)

Niladri Biswas

SQL 2008 SQLPUBWIZ Command Line Failure

Member 400112012-Jul-09 12:46

Member 4001120

12-Jul-09 12:46

I'm running sqlpubWiz from the command line to script my database. This command line worked with SQL 2005 - I have changed to 2008 and have got the latest version of SqlPubWiz (as far as I know - it's in the folder C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2

I run sqlpubwiz script -d mydatabsename-S localhost\mydatabase -f test.sql -targetserver 2008

All looks well - it starts with "Gathering List of Objects to Script" then changes to "Scripting Objects" and starts printing dots to show progress... then stops with

Error: Operation is not valid due to the current state of the object.

Funny thing is that it works fine if run through the GUI.

I'm all out of ideas.....

Help

.\\axxx

FROM SQL SERVER 2005 DOWNLOAD FILES FROM PSFTP

ps_prakash0212-Jul-09 6:15

ps_prakash02

12-Jul-09 6:15

Hi,

My Requirement:
My Client will place inputs files in psftp. we need to write a procedure in sql server 2005, which has to periodically check the psftp for files if any files found in psftp then the sql procedure has to download those files to local folder.

Actually i have written a batch file which login to psftp and download the files to local folder. if i run the batch file manually the batch file download files from PSFTP to local folder, but if i execute the same batch file in management studio its showing as executing but its not doing anything and after some seconds management studio is not responding.

if anybody can tell me how to do this it will be great help for me.

the command i used is
exec master..xp_cmdshell '\\server1\file\download.bat'

Thanks & Regards,
P.Prakash

Re: FROM SQL SERVER 2005 DOWNLOAD FILES FROM PSFTP

Jan Wild21-Dec-09 4:30

Jan Wild

21-Dec-09 4:30

I have the same problem.
Did you found a solution?

How do i retrieve data from an MS sql database hosted on a web server

ChiSmile11-Jul-09 4:16

ChiSmile

11-Jul-09 4:16

Hello Gurus!
Please can someone help me in the following problem:
Am finding it very difficult connecting to MS sql database hosted on a web server from my machine through a stand alone application i developed.
I have followed this http://support.microsoft.com/kb/914277[^]

To configure both my machine and the Sql Server Management Studio Express, but this error keeps coming up:

"An error has occured while establishing a connection to the server. When connecting to SQL server 2005, this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (provider: SQL network Interfaces, error : 25 - Connection String is noy valid)"

my connection String looks like below:

"Data Source = https://sbbMedical.com\localhost; Initial Catalog = sbbmhcom_medihealth;Integrated Security=False;User Id= '" & UserIdTextBox.Text & "'; Password= '" &PasswordTextBox.Text &"'"

NOTE: I have for example the database to be sbbmhcom_medihealth, with the password: obisi1 and User id: obisimora. But my domain user ID is "medics" and password is "nevermind"

How do I adjust the connection string.
what am I missing.

Thanks in advance

Re: How do i retrieve data from an MS sql database hosted on a web server

ChiSmile12-Jul-09 4:39

ChiSmile

12-Jul-09 4:39

Please can anyone help me.
Thanks a lot in advance!!

Re: How do i retrieve data from an MS sql database hosted on a web server

Mycroft Holmes12-Jul-09 19:38

Mycroft Holmes

12-Jul-09 19:38

There are a number of issues with your message, least of which is publishing a username andpassword.

localhost is only a valid name when SQL server is installed on the local machine. Find out the name of the SQL server installed at sbbMedical.

Never underestimate the power of human stupidity
RAH

Re: How do i retrieve data from an MS sql database hosted on a web server

ChiSmile13-Jul-09 9:28

ChiSmile

13-Jul-09 9:28

The name of SQL server is windows7\SQLEXPRESS

Any idea?

Thanks!

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.